Clever Geek Handbook

SQL query for top records using multiple columns

I have a Sqlite database that I want to query. My basic knowledge of SQL allows me to query most of what I need, but I'm stuck here:

The table looks like this (simplified!):

| Name            | Year | Title   | Musician 1 | Musician 2  |
| --------------- | ---- | ------- | ---------- | ----------- |
| Doe, Jon        | 2007 | Title 1 | Beatles    | The Stooges |
| May, Peter      | 2001 | Title 2 |            | Beatles     |
| Schmidt, Andrea | 1997 | Title 3 | Nick Cave  |             |

I like to request Top Musician in the 2000s

and receive output like

| Name        | Count |
| ----------- | ----- |
| Beatles     | 2     |
| The Stooges | 1     |

How would you archive this? Thank!

+3


source to share


2 answers


If I understood correctly you need this



select  count(*), name from (
    select Musician_1 as Name from table where Year >= 2000
    union all 
    select Musician_2 as Name from table where Year >= 2000
) t
group by Name 
order by count(*) desc
+3


source


you can use the union all



  select musician, count(*) from (

      select year, musician_1 as musician
      from my_table 
      union  all

      select year, musician_2
      from my_table 
  ) t 
  where t.year between 2000 and 2010
  group by t.musician
+2


source






More articles:


All Articles