Another way of doing this using CASE method is select ReleaseYear, count(distinct case when Genre='Sci-Fi' then Title* else null end) as 'Sci Fi', count(distinct case when Genre='Crime' then Title* else null end) as 'Crime', count(distinct case when Genre='Romance' then Title* else null end) as 'Romance', count(distinct case when Genre='Action' then Title* else null end) as 'Action', count(distinct case when Genre='Drama' then Title* else null end) as 'Drama' from movies group by 1 order by 1; * (Title or MovieId . either of the can be taken in case statement as both will be unique )
Interesting video, thanks. Just wondering though, is there a way to do dynamic pivoting, so that you don't have to type out the values in the columns - in the case where you have a lot of different values in the columns?
Another way of doing this using CASE method is
select
ReleaseYear,
count(distinct case when Genre='Sci-Fi' then Title* else null end) as 'Sci Fi',
count(distinct case when Genre='Crime' then Title* else null end) as 'Crime',
count(distinct case when Genre='Romance' then Title* else null end) as 'Romance',
count(distinct case when Genre='Action' then Title* else null end) as 'Action',
count(distinct case when Genre='Drama' then Title* else null end) as 'Drama'
from movies
group by 1
order by 1;
* (Title or MovieId . either of the can be taken in case statement as both will be unique )
Interesting video, thanks. Just wondering though, is there a way to do dynamic pivoting, so that you don't have to type out the values in the columns - in the case where you have a lot of different values in the columns?
Thank you . Was helpful for me :)
That’s cool! Been using SQL for almost 2 years now and love learning stuff like this!
Thank you for sharing