Calling Joe Celko (or anyone else that knows)

15 Sep 2005 8:10 PM

Mike Labosh

(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 Mustane

15 Sep 2005 8:15 PM

--CELKO--

SELECT
    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;

15 Sep 2005 8:19 PM

Alejandro Mesa

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

Show quote

"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 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 Mustane
>
>
>

15 Sep 2005 9:50 PM

Mike Labosh

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