Record Counts - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Record Counts

Author

Topic

MTmace
Starting Member

7 Posts

Posted - 2008-10-10 : 12:22:16

I need to select from a table to get the following results

MONTH_YEAR FitsCriteria DoesNotFitCriteria TotalRecCount
10/2008 1 2 3

The source table looks like this;
MONTH_YEAR Criteria
10/2008 0
10/2008 1
10/2008 1

I can get the results of each by the following

SELECT MONTH_YEAR, COUNT(Criteria) AS FitsCriteria
FROM SOURCE_TABLE
WHERE Criteria = 0
GROUP BY MONTH_YEAR

SELECT MONTH_YEAR, COUNT(Criteria) AS DoesNotFitCriteria
FROM SOURCE_TABLE
WHERE Criteria = 1
GROUP BY MONTH_YEAR

SELECT MONTH_YEAR, COUNT(Criteria) AS TotalRecCount
FROM SOURCE_TABLE
GROUP BY MONTH_YEAR

I am looking to get the expected results without using a temp table if possible.

Thanks,

MTmace

MTmace
Starting Member

7 Posts

Posted - 2008-10-10 : 12:41:55

Problem solved:

select MONTH_YEAR, sum(FitsCriteria), sum(DoesNotFitCriteria) , sum(FitsCriteria)+ sum(DoesNotFitCriteria) total from (

SELECT MONTH_YEAR, COUNT(Criteria) AS FitsCriteria, 0 AS DoesNotFitCriteria

FROM SOURCE_TABLE

WHERE FORWARDED = 0

GROUP BY MONTH_YEAR

union

SELECT MONTH_YEAR, 0 AS FitsCriteria, COUNT(Criteria) AS DoesNotFitCriteria

FROM SOURCE_TABLE

WHERE Criteria = 1

GROUP BY MONTH_YEAR) a

group by MONTH_YEAR

MTmace

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-10 : 12:50:10

you could simply do below

SELECT MONTH_YEAR, 
COUNT(CASE WHEN Criteria=0 THEN Criteria ELSE NULL END) AS FitsCriteria,
COUNT(CASE WHEN Criteria=1 THEN Criteria ELSE NULL END) AS DoesNotFitCriteria,
COUNT(Criteria) AS TotalRecCount
FROM SOURCE_TABLE 
GROUP BY MONTH_YEAR

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources