CODEWITHSUNDEEP
mysql
fab
fab

Reputation: 174

MySQL nested count statement

imagine that I have 2 tables and I would like to calculate the best selling artists:

This query returns e.g.:

  sells    | barcode |   artist
 ---------------------------------
   1000        123       jdoe
    500        223       kloe
    100        321       jdoe

How can I calculate that jdoe complexively sold 1100 music?

Thanks in advance for any hint

Upvotes: 0

Views: 1008

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You should change your group by from barcode to artist (and remove barcode from the select statement):

SELECT COUNT(m.barcode) AS sells, artist
FROM music m LEFT JOIN
     artists a
     ON m.barcode = a.barcode
GROUP BY artist
ORDER BY sells DESC;

Upvotes: 1

srakl
srakl

Reputation: 2619

SELECT COUNT(m.barcode) AS popular, m.barcode, a.artist, SUM(a.sell) 
FROM `music` AS m 
LEFT JOIN `artists` AS a 
ON (m.barcode = a.barcode)
WHERE a.artist = 'jdoe'
GROUP BY m.barcode 
ORDER BY `popular` DESC

Upvotes: 0

Related Questions