CODEWITHSUNDEEP
mysqlsubquery
jd182
jd182

Reputation: 3260

Sort by result of sub query in MySQL

I have one table which contains a record for all of the downloadable items and another table that contains a record of each download that includes the download date and the id of the item and the user who downloaded it.

When I get a list of items from the database I want the query to look up how many times an item has been downloaded and include this with the other item data.

Here's my SQL:

SELECT t1.*, 
(SELECT COUNT(*) 
    FROM tl_downloads 
    WHERE item_id = t1.id
    AND download_date > ?) 
AS downloads_count 
FROM tl_items AS t1 
ORDER BY downloads_count DESC

The above query works fine in the sense that I get an extra column in the results called 'downloads_count' which contains the correct count but it will not let me order by this value.

What do I need to do to be able to do order my results by the value returned in the sub query?

Let me know if you need any more info!

Edit - Sample data

tl_items                tl_downloads

id  name                id  item_id  download_date
1   Item 1              1   1        1385964000 
2   Item 2              2   1        1385964000
3   Item 3              3   3        1385964000

The results I expect would be:

Item 1 - Downloads 2
Item 3 - Downloads 1
Item 2 - Downloads 0

Thanks

Upvotes: 7

Views: 28938

Answers (3)

Bernhard Kraus
Bernhard Kraus

Reputation: 329

Not yet tested but another approach:

SELECT t1.* 
FROM tl_items AS t1 
ORDER BY 
(SELECT COUNT(*) totalCount
FROM tl_downloads 
WHERE item_id = t1.id
AND download_date > ? ORDER BY totalCount [desc])

Right? Maybe faster because of no joins.

Upvotes: 0

Slowcoder
Slowcoder

Reputation: 2120

Expecting the 0 downloads to be included in the results and having download date constraint seems to be contradicting for me unless i am overlooking into it.

SELECT tt1.id, COUNT(tt2.id) AS DOWNLOAD_COUNT
FROM t1_items tt1
LEFT JOIN t1_downloads tt2 ON tt1.id = tt2.item_id
WHERE download_date > 1385963000 OR download_date IS NULL
GROUP BY tt1.id
ORDER BY DOWNLOAD_COUNT DESC

The above query will return the expected result.

http://www.sqlfiddle.com/#!2/29b39/13

Upvotes: 1

John Woo
John Woo

Reputation: 263703

try,

SELECT  t1.*, 
        s.totalCount AS downloads_count 
FROM    tl_items AS t1 
        LEFT JOIN
        (
            SELECT  item_id, COUNT(*) totalCount 
            FROM    tl_downloads 
            WHERE   download_date > ?
            GROUP   BY item_id
        )  s ON s.item_id = t1.id
ORDER   BY downloads_count DESC

Upvotes: 17

Related Questions