Donate. I desperately need donations to survive due to my health

Get paid by answering surveys Click here

Click here to donate

Remote/Work from Home jobs

Find the average and total revenue by each subcategory for the categories which are among top 5 categories in terms of quantity sold

on

I have written the following code for the question.

SELECT TOP 5 
    MAX(Qty) AS Quantity,
    AVG(total_amt) AS 'Average Revenue',
    SUM(total_amt) as 'Total Revenue',
    prod_cat as 'Product Category',
    prod_subcat as 'Product Sub Category'
FROM Transactions$
LEFT JOIN prod_cat_info$ 
    ON prod_cat_info$.prod_sub_cat_code=Transactions$.prod_subcat_code
GROUP BY prod_subcat,prod_cat
ORDER BY MAX(Qty) desc,AVG(total_amt) desc ,SUM(total_amt) desc

On running the code it gives me this output.

How can I change the code such that the top 5 category sold in terms of quantity have all the sub categories present in those 5 categories and the average and total revenue are sorted according to those sub categories?

As for the format of the output, i dont have an exact format for the output but what i need is, for example if prod_cat 'Clothing' is in the top 5 as per quantity sold then i want to see the average and total revenue for all the sub categories present for clothing like 'Mens','Women','Kids'

Tansactions$

transaction_id	cust_id	tran_date	prod_subcat_code	prod_cat_code	Qty	Rate	Tax	total_amt	Store_type
80712190438	270351	28-02-2014	1	1	-5	-772	405.3	-4265.3	e-Shop
29258453508	270384	27-02-2014	5	3	-5	-1497	785.925	-8270.925	e-Shop
51750724947	273420	24-02-2014	6	5	-2	-791	166.11	-1748.11	TeleShop
93274880719	271509	24-02-2014	11	6	-3	-1363	429.345	-4518.345	e-Shop
51750724947	273420	23-02-2014	6	5	-2	-791	166.11	-1748.11	TeleShop
97439039119	272357	23-02-2014	8	3	-2	-824	173.04	-1821.04	TeleShop
45649838090	273667	22-02-2014	11	6	-1	-1450	152.25	-1602.25	e-Shop
22643667930	271489	22-02-2014	12	6	-1	-1225	128.625	-1353.625	TeleShop
79792372943	275108	22-02-2014	3	1	-3	-908	286.02	-3010.02	MBR
50076728598	269014	21-02-2014	8	3	-4	-581	244.02	-2568.02	e-Shop

Prod_cat_info$

prod_cat_code    prod_cat       prod_sub_cat_code    prod_subcat
1               Clothing        4                    Mens
1                Clothing       1                    Women
1                Clothing       3                    Kids
2                Footwear       1                    Mens
2                Footwear       3                    Women
2                Footwear       4                    Kids
3                Electronics    4                    Mobiles
3                Electronics    5                    Computers
3                Electronics    8                    Personal Appliances
3                Electronics    9                    Cameras
3                Electronics    10                   Audio and video
4                Bags           1                    Mens
4                Bags           4                    Women
5                Books          7                    Fiction
5                Books          12                   Academic
5                Books          10                   Non-Fiction
5                Books          11                   Children
5                Books          12	                 Academic
5                Books	        10	                 Non-Fiction
5                Books	        11           	     Children

Comments

Post a Comment