Rollup subtotal as percent of grand total

Database
Enthusiast

Rollup subtotal as percent of grand total

SELECT p.product_name, numOfCalls *100.000/SUM(numOfCalls) OVER (PARTITION BY p.product_name ORDER BY p.product_name)
FROM LAB_ONE.ft_product ftp
JOIN LAB_ONE.dim_s_detail dsd
ON ftp.s_detail_dim_ck = dpd.s_detail_dim_ck
JOIN LAB_ONE.dim_s_syn dss
ON ftp.s_dim_ck = dss.s_dim_ck
JOIN LAB_ONE.dim_product p
ON dss.product_dim_ck = p.product_dim_ck
WHERE dss.line_of_business <> 'NONE' AND dsd.createdon BETWEEN '2016-01-01 00:00:00.000' AND '2016-04-01 00:00:00.000'
GROUP BY ROLLUP (p.product_name, numOfCalls)
ORDER BY 1

I need a query that will calculate the percent of calls made for a product against the grand total of calls.  I tried a lot of variations of this query and this is the best one so far.  I get the percentage for the product total only, not the grand total.  I think it might be the Group By Rollup having 2 columns instead of 1 but if I don't include the 2nd column I get this error.  3504 - selected non-aggregate values must be part of the associated group.

Does anyone know how to get this to calculate against the grand totals?

I get this when I just sum the calls.  Everything rolls up.  ? = 3141817, Product 1 = 49153, Product 2 = 310032, Product 3 = 49308 etc.

This is what I get with the attached query - 39 rows for product 1:  Product 1 = 0.41, Product 1 = 2.03, Product 1 = 3.65, etc.

What I want to see is Product 1 = 0.02 (or 2), Product 2 = 0.10 (or 10), Product 3 = 0.02 (or 2).

Thanks.

7 REPLIES
Enthusiast

Re: Rollup subtotal as percent of grand total

I tried this select which also returns multiple rows for each product - 

SELECT   p.product_name, SUM(numOfCalls), CAST (numOfCalls AS DECIMAL (5,2)) / SUM(NULLIF(numOfCalls,0))

And this query which gives me an error - Selected non-aggregate values must be part of the associated group.  No matter what I put in the Group By, it gives me the same error.

SELECT   p.product_name

FROM LAB_ONE.ft_product ftp

JOIN (

SELECT numOfCalls *100/ SUM(numOfCalls) AS pct

FROM LAB_ONE.dim_s_detail dsd) AS pctofcalls

ON ftp.s_detail_dim_ck = dsd.s_detail_dim_ck

JOIN LAB_ONE.dim_s_syn dss

ON ftp.s_dim_ck = dss.s_dim_ck

JOIN LAB_ONE.dim_product p

ON dss.product_dim_ck = p.product_dim_ck

WHERE dss.line_of_business <> 'NONE' AND dsd.createdon BETWEEN '2016-01-01 00:00:00.000' AND '2016-04-01 00:00:00.000'

GROUP BY ROLLUP (p.product_name, numOfCalls, pct, pctofcalls)

ORDER BY 1

Enthusiast

Re: Rollup subtotal as percent of grand total

IF you are trying to group by  p.product_name, then use only that column in the GROUP BY clause

Enthusiast

Re: Rollup subtotal as percent of grand total

I tried that but I get an error message that says  Selected non-aggregate values must be part of the associated group.

Enthusiast

Re: Rollup subtotal as percent of grand total

SELECT p.product_name, SUM(numOfCalls) TotalCalls,  SUM(CAST(numOfCalls AS FLOAT)) / SUM(TotalCalls) PercentOfCalls
FROM LAB_ONE.ft_product ftp
JOIN LAB_ONE.dim_s_detail dsd
ON ftp.s_detail_dim_ck = dpd.s_detail_dim_ck
JOIN LAB_ONE.dim_s_syn dss
ON ftp.s_dim_ck = dss.s_dim_ck
JOIN LAB_ONE.dim_product p
ON dss.product_dim_ck = p.product_dim_ck
WHERE dss.line_of_business <> 'NONE' AND dsd.createdon BETWEEN '2016-01-01 00:00:00.000' AND '2016-04-01 00:00:00.000'
GROUP BY ROLLUP (p.product_name)
ORDER BY 1

I'm getting closer to the answer but I don't understand why I get these errors.  If I divide the sum by the same column I can get output but of course it's all 1's.  If I sum the calls as alias TotalCalls, then divide by TotalCalls, I get an error that I can't nest aggregate operations. 

Then I try a subquery but to get the correct math I have to refer to the alias TotalCalls in the first select which is not recognized because it's reading the logic in the second select first.   

If I put all the calculations into a subquery, it doesn't recognize the object 'dsd'. 

Can someone tell me what I'm doing wrong?

Enthusiast

Re: Rollup subtotal as percent of grand total

Also, I should have said derived table, not subquery.  Apologies for any confusion.

Senior Apprentice

Re: Rollup subtotal as percent of grand total

I don't think you need ROLLUP, looks like you just want an OLAP-sum on top of the aggregate SUM:

SELECT p.product_name, SUM(numOfCalls) TotalCalls,  
100.00 * TotalCalls/ SUM(TotalCalls) OVER () AS PercentOfCalls
FROM LAB_ONE.ft_product ftp
JOIN LAB_ONE.dim_s_detail dsd
ON ftp.s_detail_dim_ck = dpd.s_detail_dim_ck
JOIN LAB_ONE.dim_s_syn dss
ON ftp.s_dim_ck = dss.s_dim_ck
JOIN LAB_ONE.dim_product p
ON dss.product_dim_ck = p.product_dim_ck
WHERE dss.line_of_business <> 'NONE' AND dsd.createdon BETWEEN '2016-01-01 00:00:00.000' AND '2016-04-01 00:00:00.000'
GROUP BY p.product_name
ORDER BY 1
Enthusiast

Re: Rollup subtotal as percent of grand total

That is exactly what I needed.  Thank you very much.  I was so sure I need ROLLUP.