user1293304 user1293304 - 2 months ago 16
SQL Question

Display distinct groups in a database and for each one count number of distinct users

Hi I am facing with this issue. You have a database let's say musicbands, containing an auto increment id column, band_name as var char, genre as var char, join_date as DateTime in format for example 2016-09-01 10:37:06.

What I want to do is to display number of distinct genres in my database, and for each one display unique number of bands and next to it the timestamp of last joined band of that genre.

I want to user Oracle SQL
Did a lot of queries but no luck. Thanks in advance.

Answer

SQL 101

select 
genre, 
count(distinct band_name) as total_band_names, 
max(join_date) as max_join_date
from musicbands
group by genre
order by genre

formatting a date in a certain format is however a bit different depending on what RDBMS you use.

But for the Oracle RDBMS you can use TO_CHAR for it.

to_char(max(join_date), 'yyyy-mm-dd hh24:mi:ss') as max_join_date