CODEWITHSUNDEEP
sqloracle-database
Abiodun Adeoye
Abiodun Adeoye

Reputation: 1095

How do I merge two results of an SQL query

I am trying to merge results of two sql queries as one

query 1:

SELECT COUNT(class) as show, year
FROM lee
where class = 'Show'
GROUP BY year

Result

show         year
185          2016
296          2020
655          2019

Query 2

SELECT COUNT(class) as movie, year
FROM lee
where class = 'movie'
GROUP BY year

Result

   movie           year
    556          2016
    987          2020
    690          2019

How do I achieve something like this:

        movie          show           year
        556            185           2016
        987            296           2020
        690            655           2019

Upvotes: 0

Views: 35

Answers (3)

Vahram Danielyan
Vahram Danielyan

Reputation: 309

Mine solution is not the most beautiful but it's for give an other option

SELECT m.movie, s.show, m.YEAR FROM
(SELECT COUNT(1) AS movie, YEAR FROM LEE WHERE CLASS = 'movie' GROUP BY YEAR) m
JOIN (SELECT COUNT(1) AS show, YEAR FROM LEE WHERE CLASS = 'show' GROUP BY YEAR) s 
ON (s.YEAR = m.YEAR);

Upvotes: 0

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

You can use case when statement for choosing class for counting.

SELECT count(CASE WHEN class = 'movie' THEN 1 END) as movie,
       count(CASE WHEN class = 'show' THEN 1 END) as show,
       year
FROM lee
GROUP BY year;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Use conditional aggregation:

SELECT SUM(CASE WHEN class = 'show' THEN 1 ELSE 0 END) as show,
       SUM(CASE WHEN class = 'movie' THEN 1 ELSE 0 END) as movie,
       year
FROM lee
GROUP BY year;

Upvotes: 0

Related Questions