# SQL Query Calculation

I have the need to perform a calculation that takes Dollar Sales/Units Sold.

Here is how I calculate Dollar Sales in my query
``````SUM(CONVERT(MONEY, CASE WHEN ( ord_date BETWEEN @WkStartDate AND @WkEndDate )
THEN ( DATA.total_qty * DATA.price
* DATA.co_rate )
ELSE ( 0 )
END)) AS [Wk_TY_Sales] ,
``````

Here is how I calculate Unit Sales
``````SUM(CONVERT(MONEY, ( CASE WHEN ( ord_date BETWEEN @WkStartDate AND @WkEndDate
AND DATA.line_status = 'I'
) --  CONDITIONAL
THEN ( DATA.total_qty * DATA.price
- ( DATA.total_qty * DATA.price )
* ( ISNULL(zzxdiscr.disc_perc, 0)
/ 100 ) )
ELSE ( 0 )
END ) * DATA.co_rate)) AS [Wk TY Unit Sales]
``````

I'm just not certain of the syntax to combine these two so that I get the [Wk TY AUR] more importantly I need to account for the divide by zero error since some weeks have a 0.00 for dollar sales/unit sales
###### Who is Participating?

Commented:
Does this work for you?

, CASE WHEN SUM(CASE WHEN ( ord_date BETWEEN '8/21/2016' AND '8/27/2016'
AND DATA.line_status = 'I'
) --  CONDITIONAL
THEN ( DATA.total_qty * DATA.price
- ( DATA.total_qty * DATA.price )
* ( ISNULL(0, 0)
/ 100 ) )
ELSE ( 0 )
END * DATA.co_rate) = 0 THEN 0
ELSE SUM(CASE WHEN ( ord_date BETWEEN '8/21/2016' AND '8/27/2016'
AND DATA.line_status = 'I'
) --  CONDITIONAL
THEN ( DATA.total_qty * DATA.price
- ( DATA.total_qty * DATA.price )
* ( ISNULL(0, 0)
/ 100 ) )
ELSE ( 0 )
END * DATA.co_rate) / SUM(CASE WHEN ( ord_date BETWEEN '8/21/2016' AND '8/27/2016'
AND DATA.line_status = 'I'
) --  CONDITIONAL
THEN ( DATA.total_qty * DATA.price
- ( DATA.total_qty * DATA.price )
* ( ISNULL(0, 0)
/ 100 ) )
ELSE ( 0 )
END * DATA.co_rate) END AS [Wk \$ / Units]

If the Unit Sales are 0, this returns 0.  Otherwise it divides the two values.
0

DeveloperCommented:
I prefer this pattern when dealing with operations on aggregations:

``````SELECT *, CASE WHEN u.Units = 0 THEN 0 ELSE u.Sales / u.Units END
FROM (SELECT 99 AS Sales, 7 AS Units) u
``````

To apply this to your specific query:

``````SELECT *, CASE WHEN u.[Wk TY Unit Sales] = 0 THEN 0 ELSE u.k_TY_Sales / u.[Wk TY Unit Sales] END
FROM (/*  Your original query goes here  */) u
``````
0

DeveloperCommented:
You could also use a Common Table Expression:

``````;WITH cte (Units, Sales) AS (
SELECT 7 AS Units, 99 AS Sales

) SELECT *, CASE WHEN Units = 0 THEN 0 ELSE Sales/Units END
FROM cte
``````
0

Senior DBACommented:
I prefer to push the calcs down into CROSS APPLYs in order to assign an alias name to them.  That keeps the main code less "cluttered" to me.

SELECT /*...,*/
SUM(Wk_TY_Sales) AS Wk_TY_Sales,
SUM([Wk TY Unit Sales]) AS [Wk TY Unit Sales],
CASE WHEN SUM([Wk TY Unit Sales]) = 0.0 THEN 0.0
ELSE SUM([Wk_TY_Sales]) / SUM([Wk TY Unit Sales]) END AS [Wk TY AUR]
FROM dbo.tablename
CROSS APPLY (
SELECT CAST(CASE WHEN ( ord_date BETWEEN @WkStartDate AND @WkEndDate )
THEN ( DATA.total_qty * DATA.price * DATA.co_rate )
ELSE ( 0 )
END AS money) AS [Wk_TY_Sales]
) AS assign_alias_names1
CROSS APPLY (
SELECT CAST(CASE WHEN ( ord_date BETWEEN @WkStartDate AND @WkEndDate AND DATA.line_status = 'I' )
THEN ( DATA.total_qty * DATA.price - ( DATA.total_qty * DATA.price ) * ( ISNULL(zzxdiscr.disc_perc, 0) / 100.0 ) )
ELSE ( 0 )
END * DATA.co_rate AS money) AS [Wk TY Unit Sales]
) AS assign_alias_names2
0

Author Commented:
Thank you for all of your help.
0

DeveloperCommented:
@mburk1968 - if one or more of these responses answered your question, you can close the question and select the answers.
0

Author Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.