Count and Subqueries - 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

Count and Subqueries

Author

Topic

sqlblaze
Starting Member

1 Post

Posted - 2010-10-12 : 19:41:29

Help.I am trying to perform 2 counts on the same set of data, but the results I get don't group the data by month. I need the count to seperate it by discards and total, but it just gives me the same total for all months. See example below. Thanks

SQL CODE:

SELECT
billing.`Month Closed`,
(Select count(billing.Group) FROM billing WHERE billing.Group LIKE '%discard%') AS DISCARD,
(Select count(billing.Group) FROM billing) AS TOTAL
FROM billing
GROUP BY billing.`Month Closed`;

RESULTS: NOT GROUPED BY MONTH

MONTH CLOSED DISCARD TOTAL
2010 04 Apr 400 22427
2010 05 May 400 22427
2010 07 Jul 400 22427
2010 08 Aug 400 22427
2010 09 Sep 400 22427

singularity
Posting Yak Master

153 Posts

Posted - 2010-10-12 : 21:50:26

[code]
select billing.'Month Closed',
count(case when billing.Group like '%discard%' then billing.Group end) as DISCARD,
count(billing.Group) as TOTAL
from billing
group by billing.'Month Closed';
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources