user1293304 user1293304 - 1 year ago 78
SQL Question

Oracle SQL - Display distinct groups in a database and for each one count number of distinct users per group and timestamp of last user that joined

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 Source

SQL 101

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download