Re: Drawing a blank on some SQL
От | Rob Sargent |
---|---|
Тема | Re: Drawing a blank on some SQL |
Дата | |
Msg-id | 4D558B34.50901@gmail.com обсуждение исходный текст |
Ответ на | Drawing a blank on some SQL (Aaron Burnett <aburnett@bzzagent.com>) |
Список | pgsql-sql |
On 02/11/2011 11:46 AM, Aaron Burnett wrote: > > Hi, > > I'm just drawing a blank entirely today and would appreciate some help on > this. > > The long and short; there are 12 distinct activities that need to be queried > on a weekly basis: > > SELECT count(activity_id), activity_id > FROM foo_activity > WHERE created >= '01/01/2011' and created < '01/08/2011' > GROUP BY 2 > ORDER BY 2; > > It gives me this answer, which is correct: > > count | activity_id > -------+--------------------- > 1502 | 1 > 11 | 2 > 2 | 3 > 815 | 4 > 4331 | 7 > 30 | 9 > 1950 | 10 > 7 | 11 > 67 | 12 > > But what I need to see is if there are no activities for the particular > activity_id that week, that it lists the count as 0 and lists the > activity_id associated like this: > > count | activity_id > -------+--------------------- > 1502 | 1 > 11 | 2 > 2 | 3 > 815 | 4 > 0 | 5 > 0 | 6 > 4331 | 7 > 0 | 8 > 30 | 9 > 1950 | 10 > 7 | 11 > 67 | 12 > > Thanking you in advance for any help on this. The caffiene seems to be not > working well today. > > Aaron > > Do you have a table which lists exhaustively the know activity_id values in the system. You may need to 'select distinct activity_id from foo_activity' to get the complete list, then left join against that (or the existing list) in your count. You need something to supply the 5,6 and 8.
В списке pgsql-sql по дате отправления: