Calling Joe Celko (or anyone else that knows)

(I think you were the one that posted this)
Someone asked how to count two different things in one select statement.
I had never seen it before and I was deeply impressed.
A coworker is asking me how to do two different counts grouped by different
things...
Some column called IVP can be 1, 0 or NULL. He wants to execute a statement
that generates a one-line result set of counts that looks like this
| 1Count | 0Count | NullCount |
| 47 | 23 | 5 |
Something like this:
SELECT
COUNT([?]) AS 1Count,
COUNT([?]) AS 0Count,
COUNT([?]) AS NullCount
FROM TheTable
-- GROUP BY [?] -- is this even required?
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneSELECT
SUM (CASE WHEN foo = 0 THEN 1 ELSE 0 END) AS tally_0,
SUM (CASE WHEN foo = 1 THEN 1 ELSE 0 END) AS tally_1,
SUM (CASE WHEN foo IS NULL THEN 1 ELSE 0 END) AS tally_null,
FROM TheTable;|||Try:
select
sum(case when c1 = 1 then 1 else 0 end) as count_1,
sum(case when c1 = 0 then 1 else 0 end) as count_0,
sum(case when c1 is null then 1 else 0 end) as count_null
from
t1
AMB
"Mike Labosh" wrote:

> (I think you were the one that posted this)
> Someone asked how to count two different things in one select statement.
> I had never seen it before and I was deeply impressed.
> A coworker is asking me how to do two different counts grouped by differen
t
> things...
> Some column called IVP can be 1, 0 or NULL. He wants to execute a stateme
nt
> that generates a one-line result set of counts that looks like this
> | 1Count | 0Count | NullCount |
> | 47 | 23 | 5 |
> Something like this:
> SELECT
> COUNT([?]) AS 1Count,
> COUNT([?]) AS 0Count,
> COUNT([?]) AS NullCount
> FROM TheTable
> -- GROUP BY [?] -- is this even required?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>
>|||Thanks everyone; the coworker was quite impressed.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conquerer.
Kill them all and you're a god." -- Dave Mustane