SQLite COUNT and LEFT JOIN - how to combine?

Question

There are two tables: one (P) containing a list of products, the other (H) containing a history of consumed products. Each product can be consumed 0 or more times. I need to build a query that will return all products from P along with the number of times each product was used up, sorted by the time it was 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 the history table contains data, but if it is not, the result is incorrect. What am I doing wrong?

Answer 1

You will need group by to get the counters you need. You also need to apply the aggregate function to H.Date , otherwise it is not clear which date to choose:

 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 chose MAX(H.Date) to create the date of last consumption; if you need another date from H , change the aggregation function.

I'm not sure if sqlite allows sorting by alias; if it is not, replace

 ORDER BY Count DESC 

with

 ORDER BY COUNT(H.P_ID) DESC