same aggregate but 2 different 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

General SQL Server Forums

New to SQL Server Programming

same aggregate but 2 different results

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-11-14 : 09:07:40

Hello there.

I have a script that will give me three columns and a count(column_ref)

see below.

desc1/ desc2/ desc3/ count(column_ref)
where clause.
group by
order by

that i can acheive easily, but i want to have another count(column_ref) next to the first one using same description columns but with different criterias,

like

desc1/ desc2/ desc3/ count(column_ref)as 'passed', count(column_ref)as 'AllOthers'

does anyone have any idea?

Regards

Rob

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-11-14 : 09:13:27

something like this


count( case when somecol =  somecondition then column_ref end ) as passed,
count( case when somecol <> somecondition then column_ref end ) as allothers

KH
[spoiler]Time is always against us[/spoiler]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-14 : 09:16:02

One of these

COUNT(CASE WHEN desc1 = 'All others' THEN column_ref END) AS 'All others'
-- or
SUM(CASE WHEN desc1 = 'All others' THEN 1 ELSE 0 END) AS 'All others'

filter the expression within the aggregate using a case expression - I am only guessing what the expression should be. Change it as appropriate.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-11-14 : 09:16:25

o yeah, thank you nice one
will let you know

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-11-14 : 10:17:37

excellent, thank you for that.

forgot about case, already have case examples in my script bank.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources