Get sum of status and ignore it in query

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

SQL Server 2008 Forums

Transact-SQL (2008)

Get sum of status and ignore it in query

Author

Topic

san3297
Starting Member

7 Posts

Posted - 2011-03-15 : 16:39:04

This is my table structure
Date Amt status
12/1/2010 2 'B'
12/2/2010 3 ''
12/3/2010 4 'B'
12/4/2010 1 'B'
1/1/2011 5 'B'
1/2/2011 1 ''
1/3/2011 2 ''
1/4/2011 2 'B'

I want my sql query to give all amts toatl for a particular month where status is not B and Totals status with B in that month

Result Should be

Month Total TotalStatusWithB
December 4 3
January 3 2

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-15 : 16:55:30

SELECT MONTH(Date), SUM(Amt)
FROM Table
WHERE Status <> 'B'
GROUP BY MONTH(Date)

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

san3297
Starting Member

7 Posts

Posted - 2011-03-15 : 17:00:39

But i will not get count of 'B' for that particular month. That is the issue for me.

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-15 : 17:14:54

I don't think your expected results are correct

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-03-15 : 17:48:04

I'm not sure I understand the requirements based on your description and expected output. But, maybe this will help?

DECLARE @T TABLE ([Date] Date, Amt INT, Status VARCHAR(1))

INSERT @T VALUES
('12/1/2010', 2, 'B')
,('12/2/2010', 3, '')
,('12/3/2010', 4, 'B') 
,('12/4/2010', 1, 'B') 
,('1/1/2011', 5, 'B')
,('1/2/2011', 1, '')
,('1/3/2011', 2, '')
,('1/4/2011', 2, 'B')


SELECT 
    DATENAME(MONTH, [Date]), 
    SUM(CASE WHEN Status = 'B' THEN Amt ELSE 0 END), 
    SUM(CASE WHEN Status = 'B' THEN 0 ELSE Amt END)
FROM @T
GROUP BY DATENAME(MONTH, [Date])

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources