|
Home > Archive > Other Oracle database topics > November 2005 > regrouping 4 sql (count) queries into 1 query with a group by, howto?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
regrouping 4 sql (count) queries into 1 query with a group by, howto?
|
|
| Didier Wiroth 2005-11-11, 7:23 am |
| 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
| |
| Michel Cadot 2005-11-11, 7:23 am |
|
"Didier Wiroth" <dont_reply@please.com> a écrit dans le message de news: 4374839d$1@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/doc...s5a.htm#1033394
Regards
Michel Cadot
| |
|
|
|
|
|