From: | Loyd Goodbar <loyd(at)blackrobes(dot)net> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: SQL problem with aggregate functions. |
Date: | 2002-07-11 22:08:52 |
Message-ID: | q60siuo880dl7qflpur9tdoel98qvk31oi@4ax.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I would suggest something like
select sum(case when f1 = 'D' then 1 else 0 end) as D_COUNT,
sum(case when f1 = 'R' then 1 else 0 end) as R_COUNT,
sum(case when f1 = 'X' then 1 else 0 end) as X_COUNT
from tab
where f1 in ('D','R','X')
Not sure what the "field group" represents.
HTH,
Loyd
On Thu, 11 Jul 2002 10:37:40 METDST, Christoph Haller <ch(at)rodos(dot)fzk(dot)de> wrote:
>>
>> I've got a table in which there is a field that can have one amongst 3
>> possible values : D, R, X. Is it possible to get in one query the count of
>> this different values.Please, note that I don't want to have a querry like
>> this :
>> "select count (*) from tab group by f1;", cause i want to get all the possible
>> count values in one row (these data are already grouped on another field).
>> To give a more accurate example, here is what I want to retrieve :
>>
>> Field group | count of D | count of R | count of X.
>>
>> Any clues ?
>> --
>What about something like
>
>
>SELECT SUM(f1_d) AS count_d,
> SUM(f1_r) AS count_r,
> SUM(f1_x) AS count_x
>FROM (
> SELECT CASE WHEN f1 = 'D' THEN 1 ELSE 0 END AS f1_d,
> CASE WHEN f1 = 'R' THEN 1 ELSE 0 END AS f1_r,
> CASE WHEN f1 = 'X' THEN 1 ELSE 0 END AS f1_x
> FROM tab ) AS foo ;
>
>Regards, Christoph
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
--
"Why, you can even hear yourself think." --Hobbes
"This is making me nervous. Let's go in." --Calvin
loyd(at)blackrobes(dot)net ICQ#504581 http://www.blackrobes.net/
From | Date | Subject | |
---|---|---|---|
Next Message | Dmitry Tkach | 2002-07-11 22:50:44 | Please, HELP! Why is the query plan so wrong??? |
Previous Message | Hannu Krosing | 2002-07-11 21:08:15 | Re: please help on query |