Thread: multiple counts using CASE

Howdy

Running Postgres 7.1.3 on RedHat Linux 7.2 kernel
2.4.7 - 10.

Wonder if it is possible to get multiple counts
from ONE query using the CASE statement.

I'm trying to set something up like this:

select count (*)
case
        when logic = '1' then 'First'
        when logic = '2' then 'Second'
        when logic = '3' then 'Third'
        else 'Error'
end as 'Count'
from temp_table
;

I wonder if this is even possible?  Maybe I can
try to create a variable (place holder) and
loop?  I don't want to have to create many
versions of one program to count for each "logic"
in the table.

Suggestions?

Thanks!

-X

"Johnson, Shaunn" <SJohnson6@bcbsm.com> writes:
> Wonder if it is possible to get multiple counts
> from ONE query using the CASE statement.

If I'm understanding you correctly, you can't do what you want with
a single aggregate function call, because that will only give you
one result.

Consider something like

select
    sum(case when logic = '1' then 1 else 0 end),
    sum(case when logic = '2' then 1 else 0 end),
    sum(case when logic = '3' then 1 else 0 end)
from ...

I also wonder whether you aren't reinventing the notion of GROUP BY.
F'r instance

select logic, count(*) from ... group by logic;

            regards, tom lane

--Thanks Tom!

--That's just what I needed!

-X

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, March 19, 2002 10:19 AM

> Wonder if it is possible to get multiple counts
> from ONE query using the CASE statement.

If I'm understanding you correctly, you can't do what you want with
a single aggregate function call, because that will only give you
one result.

Consider something like

select
        sum(case when logic = '1' then 1 else 0 end),
        sum(case when logic = '2' then 1 else 0 end),
        sum(case when logic = '3' then 1 else 0 end)
from ...

I also wonder whether you aren't reinventing the notion of GROUP BY.
F'r instance

select logic, count(*) from ... group by logic;

                        regards, tom lane