Conditional Counting

Question

Conditional Counting

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Welsh Corgi (5/12/2011)
If this is in Oracle then you should probably be using PL/SQL and posting your question to an Oracle Forum.

Ditto. We have a hard enough time with keeping language features straight for people posting 2000 or 2005 questions in 2008 forums let alone Oracle questions on a SQL Server Forum :hehe:

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

opc.three (5/11/2011)
I think you want to use SUM with a CASE statement. Maybe something along these lines:
SELECT SUM(CASE WHEN B.TRANS_TYPE = '2' THEN 1
WHEN B.TRANS_TYPE = '3'
AND 1 = 0 /* change "1 = 0" to your special condition */ THEN 1
ELSE 0
END) AS STARTS,
B.PROD_CODE AS pcode,
B.CODE AS CScode
FROM UNICA.SUBS B
WHERE B.TRANS_DATE BETWEEN @startdate AND @Enddate
AND (B.TRANS_TYPE IN ('3', '2'))
GROUP BY B.PROD_CODE,
B.CODE ;

Ah... sorry. I missed the post above before I posted. This'll work just fine.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

You CAN use a CASE statement in a COUNT... --Jeff Moden

It's just a personal preference. I just feel happier summing ones and zeroes, than I do counting non-null values. 🙂

BTW, Jeff: My wife says she would love to see a picture of you with your binkie and a beer-popsicle. 😛

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

The Dixie Flatline (5/12/2011)
You CAN use a CASE statement in a COUNT... --Jeff Moden
It's just a personal preference. I just feel happier summing ones and zeroes, than I do counting non-null values. 🙂
BTW, Jeff: My wife says she would love to see a picture of you with your binkie and a beer-popsicle. 😛

I agree which is why I followed up say OPC's sum code would do the trick. 🙂

And your wife is going to have to wait for a very, very long time. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)