query with sum and margin - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query with sum and margin

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-06-19 : 07:17:34

I have:

SELECT YEAR(CREATEDDATETIME) AS Year, SUM(LINEAMOUNTMST) AS Turnover, SUM(UNITIES) AS QTY, SUM(COSTE) AS Cost
FROM         dbo.WIKA_ES_Universal
GROUP BY YEAR(CREATEDDATETIME)

how do I get the margin (Turnover - Cost)/Turnover * 100 ?

CASE WHEN LINEAMOUNTMST <> '0' THEN SUM((LINEAMOUNTMST - COSTE) / LINEAMOUNTMST * 100) ELSE '0' END AS Margin

doesn't give the correct result... beside the fact that this requires to group by LINEAMOUNTMST which does not make sense. And to make it even worse I get a zero devision too???

Regards, Martin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-19 : 14:23:45

[code]
SELECT YEAR(CREATEDDATETIME) AS Year, SUM(LINEAMOUNTMST) AS Turnover, SUM(UNITIES) AS QTY, SUM(COSTE) AS Cost,COALESCE((SUM(LINEAMOUNTMST)- SUM(COSTE))/NULLIF(SUM(LINEAMOUNTMST),0),0) * 100
FROM dbo.WIKA_ES_Universal
GROUP BY YEAR(CREATEDDATETIME)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-06-20 : 04:30:45

Visakh, you always grant 100% reliability and service for free. This is just awesome! Thank you, Martin!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources