Conditional Count - 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)

Conditional Count

Author

Topic

mparter
Yak Posting Veteran

86 Posts

Posted - 2004-11-11 : 05:51:07

I have a table on which I'd like to count how many offers have been made. However, this has a few conditions:-

cap_stac MUST equal 'A'
If cap_dec2 IS NULL AND cap_dec1 = 'U' Then Count these
IF cap_dec1 IS NULL AND cap_dec2 = 'U' Then count these

I'm getting OK results back with the query below but I need it to only return one row for each instance of cap_ayrc, cap_crsc, cap_blok, cap_occl and cap_stac.

SELECT DISTINCT 
                      cap_ayrc, cap_crsc, cap_blok, cap_occl, cap_stac, CASE WHEN ((cap_dec2 IS NULL) AND (cap_dec1 = 'U')) THEN COUNT(cap_dec1) 
                      WHEN ((cap_dec1 IS NULL) AND (cap_dec2 = 'U')) THEN COUNT(cap_dec2) ELSE 0 END AS UOffers
FROM         dbo.srs_cap
GROUP BY cap_ayrc, cap_crsc, cap_blok, cap_occl, cap_stac, cap_dec1, cap_dec2
HAVING     (cap_stac = 'A') OR
                      (cap_stac = 'A')

this query outputs the following:-

	2003/4	ACCA        	F1	A 	A     	0
	2003/4	ACCA        	F1	A 	A     	3

when I want it to output:-

2003/4	ACCA        	F1	A 	A     	3

Help, please

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-11-11 : 07:36:44

You will need to aggregate the case statement.

Something like:

max(case.....)as UOffers

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-11 : 08:55:10

when you count something, you are essentially adding the value 1 over and over for each row, right? so you can use SUM(1) instead of count -- but make the 1 conditional using a case; if the condition is false, make it zero.

that probably made no sense, but consider this example:

select
SUM(CASE WHEN Condition1 THEN 1 ELSE 0 END) as Condition1_Count,
SUM(CASE WHEN Condition2 THEN 1 ELSE 0 END) as Condition2_Count
from
yourTable

Does this help you give your answer? more importantly, does it make sense as to how this approach would work for you? try it out, let us know if you can figure it out.

- Jeff

mparter
Yak Posting Veteran

86 Posts

Posted - 2004-11-11 : 11:49:00

Well, the MAX didn't work as it didn't like the COUNT function inside it

I'll give your idea a go jsmith, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources