Two seperate results from same table

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

Two seperate results from same table

Author

Topic

ishchopra
Starting Member

24 Posts

Posted - 2011-09-09 : 07:03:11

Hello Experts,

I have a question which you could help me on:

Currently i have one table from which i am trying to prepare stats for my reports. For Example I have a column called "Margin" now i want to see margin for Yeart to Date (YTD) and for 'current month' which is again should be based on another column called 'Calculation' (there are two values in this column i.e 'under 1m' and 'above 1m'.

Now is there any way that i can get the results writing single query to achieve this.

currently i write four different queries to get the results which are:

SELECT sum(margin) as "YTD Gross" FROM ABC
where calc = 'under 1m'

SELECT sum(margin) as "YTD Gross" FROM ABC
where calc = 'above 1m'

SELECT sum(margin) as "YTD Gross" FROM ABC
where calc = 'under 1m' and month(Data_won) = 8

SELECT sum(margin) as "YTD Gross" FROM ABC
where calc = 'above 1m' and month(Data_won) = 8

Please let me know if i can explain it more.

Waiting for replies..

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-09-09 : 07:38:51

You can put the logic that you currently have in the WHERE clause as a case expression inside the SUM function. What I mean is something like this:


select
   sum(case when calc='under 1m' then margin end) as [YTD Gross],
   sum(case when calc='under 1m' and month(Data_won)=8 then margin end) as [MTD Gross]
   -- etc
from
   ABC

ishchopra
Starting Member

24 Posts

Posted - 2011-09-09 : 10:09:39

Hey Thanks for this but it is throwing syntax error.

"syntax error (missing operator) in query expression"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 10:18:28

show your full query plzz?

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

ishchopra
Starting Member

24 Posts

Posted - 2011-09-09 : 10:37:18

SELECT

sum(case when (sales_team <> 'ME Direct' and year(start_Date) = '2011') then contribution_margin end) as [YTD Contribution Margin] ,

sum(case when (sales_team <> 'ME Direct' and year(start_Date) = '2011') and month(start_date) = 8 then contribution_margin end) as [Monthly CM]

FROM Raw_Wins_Week_36

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 10:48:29

looks fine...which editor you're using by the way?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources