CODEWITHSUNDEEP
mysqlsqlcountmultiple-columns
Track Fry
Track Fry

Reputation: 53

How to count the occurrences in multiple column in SQL

I have the following table in SQL

TV_Show | genre_1 | genre_2 |
  a     | action  | sci-fi  |
  b     | sci-fi  | comedy  |
  c     | comedy  | romance |
  d     | action  | sci-fi  |
  .     |    .    |    .    |
  .     |    .    |    .    |
  .     |    .    |    .    |

I want to run a query which will count the number of times each of the different, unique genres come up in the entire table. I want the following result. The order of this output doesn't matter:

action    2
sci-fi    3
comedy    2
romance   1
  .       .
  .       .
  .       .

What should the SQL Query be?

Edit I have already tried running the following, but it doesn't work:

SELECT genre1 OR genre2, COUNT(*) FROM tv_show GROUP BY genre1 OR genre2

Edit 2

This example is a simplification of what my actual SQL table is. My actual table has other columns with different data. But I only have two genre columns which I want to do the query on.

Upvotes: 5

Views: 2304

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

Use union all and aggregation:

select genre, count(*)
from ((select genre_1 as genre from tv_show) union all
      (select genre_2 as genre from tv_show)
     ) g
group by genre;

With a simple modification, you can add counts for each column:

select genre, count(*), sum(first), sum(second)
from ((select genre_1 as genre, 1 as first, 0 as second from tv_show) union all
      (select genre_2 as genre, 0, 1 from tv_show)
     ) g
group by genre;

Upvotes: 4

Rahul
Rahul

Reputation: 77866

You can use CASE expression along with SUM() function; group by the genere column like

sum(case when genre_1 = 'action' then 1 else 0 end) as Action,
sum(case when genre_1 = 'sci-fi' then 1 else 0 end) as Sci-Fi,
sum(case when genre_1 = 'comedy' then 1 else 0 end) as Comedy,
sum(case when genre_1 = 'romance' then 1 else 0 end) as Romance

Upvotes: 1

Related Questions