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?

Answers


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

with

ORDER BY COUNT(H.P_ID) DESC

Need Your Help

Using ORM for a multi-lingual site

php orm localization

I would like to ask for recommendations using ORM (over MySQL) and PHP. Until today I was a CodeIgniter user but now I want to code with FUEL (http://fuelphp.com) and they have a ORM.

How do I make a TreeParser in ANTLR3?

antlr antlr3

I'm attemping to learn language parsing for fun...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.