Combine two select queries in one

Question

I have three tables: history, video and user. The following two queries display for each video the number of views for French users or for German users:

SELECT V.idVideo, COUNT(H.idVideo) AS nb_fr FROM HISTORY H INNER JOIN VIDEO V ON V.idVideo = H.idVideo INNER JOIN USER U ON U.idUser = H.idUser WHERE U.nationality = 'french' GROUP BY H.idVideo ORDER BY V.idVideo; 

and

 SELECT V.idVideo, COUNT(H.idVideo) AS nb_ge FROM HISTORY H INNER JOIN VIDEO V ON V.idVideo = H.idVideo INNER JOIN USER U ON U.idUser = H.idUser WHERE U.nationality = 'german' GROUP BY H.idVideo ORDER BY V.idVideo 

But how to combine these two queries with one? I would like something like this:

 idVideo | nb_fr | nb_ge ----------------------- 1 | 5 | 4 2 | 3 | 6 3 | 2 | 8 4 | 3 | 3 

Answer 1

Use case expressions to do conditional aggregation:

 SELECT V.idVideo, COUNT(case when U.nationality = 'french' then H.idVideo end) AS nb_fr, COUNT(case when U.nationality = 'german' then H.idVideo end) AS nb_ge FROM HISTORY H INNER JOIN VIDEO V ON V.idVideo = H.idVideo INNER JOIN USER U ON U.idUser = H.idUser WHERE U.nationality in ('french', 'german') GROUP BY V.idVideo ORDER BY V.idVideo; 

Note the change to GROUP BY V.idVideo , because it is the selected column.

Answer 2

You can do this using conditional aggregation with CASE EXPRESSION :

 SELECT V.idVideo, COUNT(CASE WHEN U.nationality = 'french' THEN 1 END) AS nb_fr, COUNT(CASE WHEN U.nationality = 'german' THEN 1 END) AS nb_ge FROM HISTORY H INNER JOIN VIDEO V ON V.idVideo = H.idVideo INNER JOIN USER U ON U.idUser = H.idUser GROUP BY H.idVideo ORDER BY V.idVideo 

Answer 3

Use conditional aggregation:

 SELECT H.idVideo, SUM(CASE WHEN U.nationality = 'french' THEN 1 ELSE 0 END) as nb_fr, SUM(CASE WHEN U.nationality = 'german' THEN 1 ELSE 0 END) as nb_ge FROM HISTORY H INNER JOIN USER U ON U.idUser = H.idUser GROUP BY H.idVideo ORDER BY H.idVideo; 

Also note that the request can be simplified. JOIN to VIDEO is probably not required (it is assumed that idVideo is unique, which is a very reasonable assumption).

In addition, ORDER BY and GROUP BY must use the same column reference.

Answer 4

Starting with Oracle 11.1, you can use the PIVOT operation:

 select idvideo, nb_fr, nb_ge from ( select h.idvideo, u.nationality from history h inner join user u on h.iduser = u.iduser ) pivot ( count(*) for nationality in ('french' as nb_fr, 'german' as nb_ge) ) order by idvideo ;