calculations on a group by

[ludmann]

I can’t figure out. Any ideas someone.

First I need to get the total supply, total returns, total missed sales for every value (GROUP BY O_Dim_Val) in a specific dimension (O_Dim_Id = ‘D03’) for a specific time period.

So it would be like
O_Dim_Id O_Dim_Val TotalSales TotalReturns TotalMissSales
D03 London 90897 98000 87000
D03 Sussex 8976 87776 98766

Secondly I need to do the following calculation on these rows
(Rtrn/Supply)*100 Returns, 100-((MissedSales/Supply)*100) MaxSales

I have it in two parts, but can’t turn it into one SQL statement.

SELECT (Rtrn/Supply)*100 Returns, 100-((MissedSales/Supply)*100) MaxSales
FROM
--perform the calculation on the rows below

SELECT SUM(Sent_Sum) Supply, SUM(Retnd_Sum) Rtrn, SUM(Miss_Sale_Sum) MissedSales
FROM O_Analysis
WHERE O_Dim_Id = 'D03'
AND Sale_dt BETWEEN '2003-01-01' AND '2003-12-31'
GROUP BY O_Dim_Val

To get something like this
O_Dim_Id O_Dim_Val Returns% Max Sales%
D03 London 56.34 78.90
D03 Sussex 76.43 90.99

Any help appreciated

 

[PHV]

something like this ?
SELECT O_Dim_Id, O_Dim_Val
, (SUM(Retnd_Sum)/SUM(Sent_Sum))*100 Returns
, 100-((SUM(Miss_Sale_Sum)/SUM(Sent_Sum))*100) MaxSales
FROM O_Analysis
WHERE O_Dim_Id = 'D03'
AND Sale_dt BETWEEN '2003-01-01' AND '2003-12-31'
GROUP BY O_Dim_Id, O_Dim_Val

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

better multiply by 100.00 and do so before the division of the sums, because the division of the sums might not carry any decimal places and give a result of 0

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)