In MySQL, how do we return count() without using window functions

22
March 15, 2019, at 9:10 PM

I have a table named tracks:

user_id | track_id | genre
.
.
.
.

The track_id is the primary key and and user_id is the person that uploaded the track and genre can be 'hiphop', 'rock', 'pop', 'electronic', etc.

I need to get the total count(track_id) for each user where genre is either 'rock' or 'pop'.

I can do it with by using window function:

select user_id, count(track_id) as total_tracks
from tracks
where genre = 'rock' or genre = 'pop'
group by user_id
;

But how do I do it without using any window functions. Is intersect a way to do it or doing a self-join?

READ ALSO
How to optimize a mysql query

How to optimize a mysql query

I have a problem with a mysql query that sometimes needs more than 80 secs to execute

53
Angular 7 : Cannot add or update a child row: a foreign key constraint fails

Angular 7 : Cannot add or update a child row: a foreign key constraint fails

I am stuck on this SQL error and I do not understand why:

46
“Writing configuration file failed” in MySQL Server 8.0.15

“Writing configuration file failed” in MySQL Server 8.0.15

While set up the configuration in MySQL Server 80

32
Is there a standard connection dialog for mysql?

Is there a standard connection dialog for mysql?

I have written an windows form app in C# that gets distributed to various data partnersThe purpose of this app is to connect to one of their locally controlled databases, extract data according to SQL they provide and then to send it directly to our services...

33