Count with sub querry

[Ericc]

I have this table "plays":
artist	title
artist_1	title_1
artist_1	title_1
artist_1	title_2
artist_2	title_1
I want to have something like this:
artist	title	counts
artist_1	title_1	2
artist_1	title_2	1
artist_2	title_1	1

 

I tried some querries wih SELECT DISTINCT and and GROUP BY but did not get what i wanted.

Do you have any hint for me? I guess i need a sub querry. Thank you!

 

[Funce]

Hey there! Welcome to the forums!

This one can be a bit mind boggling. But I did manage to get it to work.

It involves using GROUP BY on both `artist` and `title`. Take a look!

SELECT *, COUNT(title)
FROM plays
GROUP BY artist, title

 

[Sherin]

SELECT 
     SUM(CASE WHEN column1 IS NOT NULL THEN 1 ELSE 0 END) AS column1_count
    ,SUM(CASE WHEN column2 IS NOT NULL THEN 1 ELSE 0 END) AS column2_count
    ,SUM(CASE WHEN column3 IS NOT NULL THEN 1 ELSE 0 END) AS column3_count
FROM table