Combine two select queries in one

Question

I have three tables : history, video and user. The two following 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 combine this two queries to have only one ? I would like something like that :

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 that's the selected column.

Answer 2

You can do it 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