CODEWITHSUNDEEP
mysqlsql
Wasim
Wasim

Reputation: 5113

SQL GROUP BY multiple fields

I have the following SQL statement:

SELECT COUNT(r.id) as ratings_count, AVG(r.rating) as average_rating, b.id, b.bpm, b.producer, b.name, 
    (SELECT p.price FROM beats_pricing as p WHERE p.license = 1 AND p.beat_id = b.id) as price_1,
    (SELECT p.price FROM beats_pricing as p WHERE p.license = 2 AND p.beat_id = b.id) as price_2
    FROM beat_ratings AS r
    INNER JOIN beats AS b
    ON b.id = r.beat_id
    WHERE b.active = 1
    GROUP BY r.beat_id
    HAVING price_1 > 0
    ORDER BY average_rating DESC, ratings_count DESC
    LIMIT 50

I have a table of beats, which have multiple rows for each beat in beat_ratings which is a table of individual 5 star ratings. This SQL statement is used to get the most highly rated beats. I'm also grouping by r.beat_id to ensure there is only one row returned for each beat.

Each beat has a b.producer, and I only want to return one beat for each producer. I've tried adding it to the GROUP BY

GROUP BY r.beat_id, b.producer

But this returns the exact same results. How can I group by or return only unique rows based on the b.producerfield?

Upvotes: 0

Views: 64

Answers (1)

Barmar
Barmar

Reputation: 780673

You need to move your query into a subquery, and then group that by producer:

SELECT *
FROM (yourquery)
GROUP BY producer

Upvotes: 2

Related Questions