Count(*) of multiple criteria in one query results

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

SQL Server Development (2000)

Count(*) of multiple criteria in one query results

Author

Topic

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-26 : 15:03:16

Hi All,

I need to write a query for a single table for which the results of count(*) on multiple criteria for each column.
To explain it a bit more here is a sample. (My real scenario is too coomplex, so I make a less complicated model)


Create Table #t(A varchar(10),B varchar(10),C varchar(10),D Datetime,E varchar(10),F varchar(10))

--I need to see the results of the following hypothetical query:

Select A, B, 
	    <Count(*) of all records >, 
	    <Count(*) where C is Null>, 
	    <Count(*) where C is Not Null and F = 'XYZ'>,
	    <Count(*) where E = 'SSS'>,
	    <Count(*) where F is Not Null >
from #t
Where D between '04/01/2006' and '04/26/2006'
Group By A, B
-- Not GROUP BY the other fields : C,D,E,F

Srinika

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-26 : 16:08:47

Here is some sample data and expected results:


Create Table #t(A varchar(10),B varchar(10),C varchar(10),D Datetime,E varchar(10),F varchar(10))

insert into #t values('AAA','BBB','CCC','03/01/2006', 'EEE','FFF')
insert into #t values('ZZZ','YYY','CCC','04/02/2006', 'EEE','FFF')
insert into #t values('AAA','BBB','CCC','04/05/2006', 'SSS','FFF')
insert into #t values('AAA','BBB',NULL,'04/07/2006', 'EEE','FFF')
insert into #t values('AAA','BBB','CCC','04/08/2006', 'EEE','FFF')
insert into #t values('AAA','BBB',NULL,'04/08/2006', 'SSS','XYZ')
insert into #t values('AAA','BBB','CCC','04/09/2006', 'EEE','FFF')
insert into #t values('AAA','BBB','CCC','04/01/2006', 'EEE','FFF')
insert into #t values('AAA','BBB','CCC','04/09/2006', 'EEE',Null)
insert into #t values('AAA','BBB','CCC','04/11/2006', 'EEE',Null)
insert into #t values('PPP','QQQ','CCC','04/21/2006', 'EEE',Null)
insert into #t values('PPP','QQQ', Null,'04/21/2006', 'EEE','FFF')
insert into #t values('AAA','BBB','CCC','06/01/2006', 'EEE','FFF')
insert into #t values('AAA','BBB','CCC','05/01/2006', 'EEE','FFF')

--I need to see the results of the following hypothetical query:
/*
Select A, B, 
	    <Count(*) of all records >, 
	    <Count(*) where C is Null>, 
	    <Count(*) where E = 'SSS'>,
	    <Count(*) where F is Not Null >
from #t
Where D between '04/01/2006' and '04/26/2006'
Group By A, B
*/

Drop table #t


Expected Results
================
A       B     cnt1    cnt2    cnt3    cnt4
---     ---   ----    ----    ----    ----      
AAA	BBB	8	2	2	6
ZZZ	YYY	1	0	0	1
PPP	QQQ	2	1	0	1

Srinika

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-26 : 16:23:14

Here you go:


SELECT t.A, t.B, t.cnt1, ISNULL(u.cnt2, 0) AS cnt2, ISNULL(v.cnt3, 0) AS cnt3, ISNULL(w.cnt4, 0) AS cnt4
FROM 
(
	SELECT A, B, COUNT(*) AS cnt1
	FROM #t
	WHERE D BETWEEN '04/01/2006' AND '04/26/2006'
	GROUP BY A, B
) t
LEFT OUTER JOIN
(
	SELECT A, B, COUNT(*) AS cnt2
	FROM #t
	WHERE D BETWEEN '04/01/2006' AND '04/26/2006' AND C IS NULL
	GROUP BY A, B
) u
ON t.A = u.A AND t.B = u.B
LEFT OUTER JOIN
(
	SELECT A, B, COUNT(*) AS cnt3
	FROM #t
	WHERE D BETWEEN '04/01/2006' AND '04/26/2006' AND  E = 'SSS'
	GROUP BY A, B
) v
ON t.A = v.A AND t.B = v.B
LEFT OUTER JOIN
(
	SELECT A, B, COUNT(*) AS cnt4
	FROM #t
	WHERE D BETWEEN '04/01/2006' AND '04/26/2006' AND  F IS NOT NULL
	GROUP BY A, B
) w
ON t.A = w.A AND t.B = w.B

Tara Kizer
aka tduggan

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-26 : 16:55:08

Thanks Tara,

This should work.
I'll apply your suggestion to my real situation.

I'm off for the day - head free of a big task.

Srinika

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2006-04-26 : 17:09:49

I tend to go at this a with a different approach using CASE/SUM to simulate a count:

Select A, B,
COUNT(*) as cnt1,
SUM (
CASE
WHEN C IS NULL THEN 1
ELSE 0
END ) AS cnt2,
SUM (
CASE
WHEN E = 'SSS' THEN 1
ELSE 0
END ) AS cnt3,
SUM (
CASE
WHEN F IS NOT NULL THEN 1
ELSE 0
END ) as cnt4
from #t
Where D between '04/01/2006' and '04/26/2006'
Group By A, B

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-26 : 17:15:32

Bill's solution is definitely more efficient as mine has to keep hitting the table. His doesn't.

Tara Kizer
aka tduggan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-27 : 03:09:32

This is a kind of Cross tab. You can find such example in BOL under the topic Cross-Tab Reports

Madhivanan

Failing to plan is Planning to fail

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-04-27 : 05:02:15

I'm inclined to use COUNT(CASE WHEN <condition> THEN 1 END)
rather than SUM(CASE WHEN <condition> THEN 1 ELSE 0 END) because of the difference you get when there are no rows:

SELECT COUNT(*),
COUNT(CASE WHEN a = 1 THEN 1 END),
SUM(CASE WHEN a = 1 THEN 1 ELSE 0 END)
FROM ( SELECT 0 AS a UNION ALL SELECT 1 ) AS A
WHERE 1 = 0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-27 : 05:22:59

quote:
Originally posted by Arnold Fribble

I'm inclined to use COUNT(CASE WHEN <condition> THEN 1 END)
rather than SUM(CASE WHEN <condition> THEN 1 ELSE 0 END) because of the difference you get when there are no rows:

SELECT COUNT(*),
COUNT(CASE WHEN a = 1 THEN 1 END),
SUM(CASE WHEN a = 1 THEN 1 ELSE 0 END)
FROM ( SELECT 0 AS a UNION ALL SELECT 1 ) AS A
WHERE 1 = 0

If you want to avoid having Null when condition is not satisfied, use IsNull or Coalesce



 
SELECT COUNT(*) as All_count,
	COUNT(CASE WHEN a = 1 THEN 1 else 0 END) as count_1_count,
	COUNT(CASE WHEN a = 0 THEN 1 else 0 END) as count_0_count,
	IsNull(SUM(CASE WHEN a = 1 THEN 1 ELSE 0 END),0) as count_1_sum,
	IsNull(SUM(CASE WHEN a = 0 THEN 1 ELSE 0 END),0) as count_0_sum
FROM ( SELECT 0 AS a UNION ALL SELECT 1 ) AS A 
WHERE 1 = 0

All_count   count_1_count count_0_count count_1_sum count_0_sum 
----------- ------------- ------------- ----------- ----------- 
0           0             0             0           0

(1 row(s) affected)

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-27 : 05:49:39

quote:
COUNT(CASE WHEN a = 1 THEN 1 else 0 END) as count_1_count,

Why have you added in the else 0?

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-27 : 06:02:32

>>Why have you added in the else 0?

Well. It can be COUNT(CASE WHEN a = 1 THEN 1 END) as count_1_count

Copy and Paste problem

Madhivanan

Failing to plan is Planning to fail

pootle_flump

1064 Posts

Posted - 2006-04-27 : 06:40:44

Minor in the extreme but:
COUNT(*) where F is Not Null
is the same as
COUNT(F)
anyway

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-27 : 14:48:04

Hi All,

I was about to go by Tara's first answer, and later found that suggestion of graz is much better.
Still I didn't finish my final task, but I used the technique given and its working great.

I think Arnold's answer is also a good one, but I didn't try it as thee other one served the purpose (highly efficient). Also I'm using that Madhi's suggestion for the Nulls --> 0

Thanks All of you

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources