Re: Multiple counts

From: ciccio <ciccio_at_ciccio.it>
Date: Fri, 18 Mar 2005 18:18:04 +0100
Message-ID: <423b0e11$1_2_at_x-privat.org>


Michel Cadot wrote:
> "ciccio" <ciccio_at_ciccio.it> a écrit dans le message de news:423b0488_1@x-privat.org...
> | Hi,
> |
> | I'd like to write a query that returns a series of count(*) horizontally.
> |
> | Pratically, if I have such a table:
> |
> | Name Amount Category
> | ------------------------
> | John 1000 1
> | Jim 1500 2
> | Mary 750 1
> | George 2000 2
> | Melanie 3000 2
> |
> | I'd like to do like this:
> |
> | SELECT COUNT(*) AS CAT1 FROM MYTABLE WHERE CATEGORY = 1, SELECT COUNT(*)
> | AS CAT2 FROM MYTABLE WHERE CATEGORY = 2
> |
> | in order to obtain:
> |
> | CAT1 CAT2
> | ------------
> | 2 3
> |
> | That is what I need. How can I rewrite the query (the one above of
> | course doesn't work) in order to obtain this result?
> |
> | Thanks
>
> select sum(decode(category,1,1,0)) cat1, sum(decode(category,2,1,0)) cat2 from mytable;

[Quoted] Works. Thanks Michel!!!

[Quoted] To make it harder, if the table is:

Name Amount Category DateOfBirth


[Quoted] John    1000    1     25/12/2004
Jim     1500    2     25/12/2004
Mary     750    1     25/12/2004
George  2000    2     25/12/2004
Melanie 3000    2     25/12/2004
John    1000    1     26/12/2004
Jim     1500    2     26/12/2004
George  2000    2     26/12/2004

[Quoted] and I'd need a report of this sort:

MyDate CAT1 CAT2



[Quoted]   25/12/2004 2 3
[Quoted]   26/12/2004 1 2

[Quoted] How the query should be?

Thanks Received on Fri Mar 18 2005 - 18:18:04 CET

Original text of this message