MySQL nested count statement

Question

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

music (id, title, barcode)

artists (barcode, artist)

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

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

If you don't care about individual barcodes, why are you selecting and grouping them? — Aaron Bertrand
– Aaron Bertrand, Commented Jul 29, 2013 at 21:42

Gordon Linoff · Accepted Answer · 2013-07-30 01:10:00Z

1

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;

answered Jul 30, 2013 at 1:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

srakl · Accepted Answer · 2013-07-29 21:39:53Z

0

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

edited Jul 29, 2013 at 21:39

answered Jul 29, 2013 at 21:33

srakl

2,6192 gold badges23 silver badges33 bronze badges

Collectives™ on Stack Overflow

MySQL nested count statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related