Multiple counts

[ciccio]

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

[FredBear]

"ciccio" <ciccio@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;

Regards
Michel Cadot

[ciccio]

Michel Cadot wrote: "ciccio" <ciccio@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;

Works. Thanks Michel!!!

To make it harder, if the table is:

Name Amount Category DateOfBirth
--------------------------------
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

and I'd need a report of this sort:

MyDate CAT1 CAT2
---------------------
25/12/2004 2 3
26/12/2004 1 2

How the query should be?

Thanks

[Malcolm Dew-Jones]

ciccio (ciccio@ciccio.it) wrote:

: and I'd need a report of this sort:

: MyDate CAT1 CAT2
: ---------------------
: 25/12/2004 2 3
: 26/12/2004 1 2

: How the query should be?

Probably quite similar to the queries used in the gazzilion other sideways
reports that people have mysteriously needed to write in the last couple
of weeks.

--

This space not for rent.

[FredBear]

"ciccio" <ciccio@ciccio.it> a écrit dans le message de news:423b0e11$1_2@x-privat.org...
| Michel Cadot wrote:
| > "ciccio" <ciccio@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;
|
| Works. Thanks Michel!!!
|
| To make it harder, if the table is:
|
| Name Amount Category DateOfBirth
| --------------------------------
| 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
|
| and I'd need a report of this sort:
|
| MyDate CAT1 CAT2
| ---------------------
| 25/12/2004 2 3
| 26/12/2004 1 2
|
| How the query should be?
|
| Thanks

Use "GROUP BY" clause:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2066420

Regards
Michel Cadot