Обсуждение: 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