Re: regrouping 4 sql (count) queries into 1 query with a group by, howto?

"Didier Wiroth" <dont_reply_at_please.com> a écrit dans le message de news: 4374839d$1_at_news.vo.lu...
| Hi,
| I'm an oracle9.2/sql newbie.
|
| Is it possible to make 1 query out of the 4 queries (see below) with for
| example "group by" function?
|
| I'm actually using 4 sql queries that counts the Last_name beginning with:
| A-E (query 1, count all Last_name beginning with A to E)
| F-K (query 2, count all Last_name beginning with F to K)
| L-R (query 3, count all Last_name beginning with L to R)
| S-Z (query 4, count all Last_name beginning with S to Z)
|
| Here are the 4 queries:
| --query1
| SELECT count(*) FROM DEMANDES_AF D, ETUDIANTS E
| WHERE D.PK_DOS_NUM_ID = E.PK_DOS_NUM_ID
| AND D.DEM_ANN_ACA = '2005-2006'
| AND SUBSTR(E.ETD_NOM,1,1) BETWEEN ('A') and ('E')
|
| --query2
| SELECT count(*) FROM DEMANDES_AF D, ETUDIANTS E
| WHERE D.PK_DOS_NUM_ID = E.PK_DOS_NUM_ID
| AND D.DEM_ANN_ACA = '2005-2006'
| AND SUBSTR(E.ETD_NOM,1,1) BETWEEN ('F') and ('K')
|
| --query3
| SELECT e.etd_nom FROM DEMANDES_AF D, ETUDIANTS E
| WHERE D.PK_DOS_NUM_ID = E.PK_DOS_NUM_ID
| AND D.DEM_ANN_ACA = '2005-2006'
| AND SUBSTR(E.ETD_NOM,1,1) BETWEEN ('L') AND ('R')
| ORDER BY e.etd_nom DESC
|
| --query4
| SELECT count(*) FROM DEMANDES_AF D, ETUDIANTS E
| WHERE D.PK_DOS_NUM_ID = E.PK_DOS_NUM_ID
| AND D.DEM_ANN_ACA = '2005-2006'
| AND SUBSTR(E.ETD_NOM,1,1) BETWEEN ('S') and ('Z')
|
| thanks a lot for helping
| didier Wiroth
|
|

Yes it is possible, have a look at CASE expression: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/expressions5a.htm#1033394

Regards
Michel Cadot Received on Fri Nov 11 2005 - 05:53:56 CST