SQLite COUNT and LEFT JOIN - how to combine?
There are two tables: one (P) that contains list of products, the other one (H) contains history of products consumed. Every product can be consumed 0 or more times. I need to build a query that will return all products from P along with number of times every product has been consumed, sorted by the times it's been consumed. Here is what I did:
SELECT P.ID, P.Name, H.Date, COUNT(H.P_ID) as Count FROM P LEFT JOIN H ON P.ID=H.P_ID ORDER BY Count DESC
This seems to work only if history table contains data, but if it does not - the result is incorrect. What am I doing wrong?
You need a group by to get the counts that you need. You also need to apply an aggregate function to H.Date, otherwise it is not clear which date to pick:
SELECT P.ID, P.Name, COUNT(H.P_ID) as Count, MAX(H.Date) as LastDate FROM P LEFT JOIN H ON P.ID=H.P_ID GROUP BY P.ID, P.Name ORDER BY Count DESC
I picked MAX(H.Date) to produce the date of last consumption; if you need a different date from H, change the aggregating function.
I am not sure if sqlite lets you sort by alias; if it does not, replace
ORDER BY Count DESC
ORDER BY COUNT(H.P_ID) DESC