0

I have table A :

genre   collection
horror   20
action   50
rom-com  30
sci-fi   90

and table B:

MovieId   genre     performance
1         sci-fi     Hit
2         rom-com    Hit
3         sci-fi     Flop
4         rom-com    Hit
5         rom-com    Hit
6         horror     Flop
7         horror     Flop
8         sci-fi     Hit
9         rom-com    Hit

I am trying to get a count of hits per genre and also sum of box office collection of movies which were a Hit .

So I trying to get something like:

genre     hit_movies     total_collection
horror     0              0
action     0              0
rom-com    4              120
sci-fi     2              180

I am trying this by using a subquery:

select genre, sum(total_collection) as total_collection  
    ,(select count(*) from  A  where genre = A.genre and A.performance = 'Hit') as hit_movies
    from 
    (select A.genre as genre ,
        (select sum(B.collection) from B  where B.genre = A.genre and A.performance = 'Hit')
        as total_collection from A  
    )x

group by 1

But I am not getting desired results. I am using postgresql. Any help is really appreciated.Thanks in advance.

1
SELECT tableA.genre, 
       COUNT(tableB.genre) hit_movies, 
       SUM(CASE WHEN tableB.genre IS NULL
                THEN 0
                ELSE tableA.collection
                END) total_collection
FROM tableA
LEFT JOIN tableB ON tableA.genre = tableB.genre AND tableB.performance = 'Hit'
GROUP BY tableA.genre

fiddle

Option proposed by a_horse_with_no_name (PostgreSQL-specific):

SELECT tableA.genre, 
       COUNT(tableB.genre) hit_movies, 
       SUM(tableA.collection) FILTER (WHERE tableB.genre IS NOT NULL)  total_collection
FROM tableA
LEFT JOIN tableB ON tableA.genre = tableB.genre AND tableB.performance = 'Hit'
GROUP BY tableA.genre

The difference - first query outputs zeroes for the genres without hits whereas second one outputs NULLs. May be changed by replacing zero with NULL (or by replace the whole CASE expression with CASE WHEN tableB.genre IS NOT NULL THEN tableA.collection END) in the first query or by wrapping the expression with COALESCE() in the second query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.