Re: Multiple counts

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Fri, 18 Mar 2005 19:02:23 +0100
Message-ID: <423b1767$0$21377$636a15ce_at_news.free.fr>

"ciccio" <ciccio_at_ciccio.it> a écrit dans le message de news:423b0e11$1_2_at_x-privat.org...
| Michel Cadot wrote:
| > "ciccio" <ciccio_at_ciccio.it> a écrit dans le message de news:423b0488_1_at_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 Received on Fri Mar 18 2005 - 19:02:23 CET