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
kbecker87
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
Oto Shavadze
source
to share
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
scaisEdge
source
to share