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.