-1

i have a table called purchased_albums

id  album_id    member_id   date
4      8              7     2013-12-24 08:17:54
5      9              7     2013-12-29 11:03:35
6      8              10    2013-12-24 08:17:54

im trying to sort it out by most purchased albums. that is, it should count the most repeated album_id and sort it out based on that. my query keeps returning null.

$q="SELECT COUNT(album_id) as tot,album_id,date  FROM purchased_albums ORDER BY tot DESC";
Sir Lojik
  • 1,409
  • 7
  • 24
  • 45

4 Answers4

2

Please go through this code may be you will find your exact requirement.........

  $q="SELECT COUNT(album_id) as tot,album_id,date FROM purchased_albums GROUP BY album_id ORDER BY album_id DESC";
Sandeep Vishwakarma
  • 566
  • 1
  • 5
  • 17
1
SELECT 
    COUNT(album_id) as tot,album_id,date  
FROM 
    purchased_albums 
GROUP BY album_id
ORDER BY tot DESC";
Ahmed Siouani
  • 13,701
  • 12
  • 61
  • 72
Ashish
  • 271
  • 2
  • 15
1

You can use a join to get the count per album, then order by the resulting quantity:

SELECT pa.*
FROM purchased_albums pa
INNER JOIN
(
    SELECT COUNT(*) qty_purchased, album_id
    FROM purchased_albums
    GROUP BY album_id
) c ON pa.album_id = c.album_id
ORDER BY c.qty_purchased DESC
lc.
  • 113,939
  • 20
  • 158
  • 187
1

Might something like this help you?

SELECT `album_id`, `date`, COUNT(*) `purchases`
FROM `purchased_albums`
GROUP BY `album_id`
ORDER BY `purchases` DESC

To pick out albums, that are purchased more then e.g. 3 times, use HAVING

SELECT `album_id`, `date`, COUNT(*) `purchases`
FROM `purchased_albums`
GROUP BY `album_id`
HAVING `purchases` > 3
ORDER BY `purchases` DESC
Jonny 5
  • 12,171
  • 2
  • 25
  • 42