Need to Get Data from a table which contains unique id with different category in mysql

Question

Hello all,

My Table Structure is:

artist_id category

1 Song

1 Song

1 Song

1 Video

1 Video

Now, my output must be

id category total

1 Song 3

1 Video 2

How this can done by using MYSQL.

Answer 1

I think this should work (untested):

select artist_id, category, count(*) as total from table group by artist_id, category