Обсуждение: Drawing a blank on some SQL
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
Not tested.<br /><br />1. select count(t2.activity_id),<br /> t1.activity_id<br />from (select distinct activity_idfrom foo_activity) as t1, -- assumes all activities exist somewhere in table<br />left join foo_activity t2on (t1.activity_id = t2.activity_id)<br /> WHERE created >= '01/01/2011' and created < '01/08/2011'<br />group by2<br />order by 2;<br /><br />2. -- use generate_series for simple numbering scheme<br />select count(t2.activity_id)<br/> t1.x as 'activity_id"<br /> from generate_series(1,12) as t1(x), <br />left join foo_activityt2 on (t1.x = t2.activity_id)<br />WHERE created >= '01/01/2011' and created < '01/08/2011'<br />groupby 2<br />order by 2;<br /><br /><div class="gmail_quote"> On Fri, Feb 11, 2011 at 1:46 PM, Aaron Burnett <span dir="ltr"><<ahref="mailto:aburnett@bzzagent.com">aburnett@bzzagent.com</a>></span> wrote:<br /><blockquote class="gmail_quote"style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><br />Hi,<br /><br /> I'm just drawing a blank entirely today and would appreciate some help on<br /> this.<br /><br /> The longand short; there are 12 distinct activities that need to be queried<br /> on a weekly basis:<br /><br /> SELECT count(activity_id),activity_id<br /> FROM foo_activity<br /> WHERE created >= '01/01/2011' and created < '01/08/2011'<br/> GROUP BY 2<br /> ORDER BY 2;<br /><br /> It gives me this answer, which is correct:<br /><br /> count| activity_id<br /> -------+---------------------<br /> 1502 | 1<br /> 11 | 2<br /> 2 | 3<br /> 815 | 4<br /> 4331 | 7<br /> 30 | 9<br /> 1950 | 10<br /> 7 | 11<br /> 67 | 12<br/><br /> But what I need to see is if there are no activities for the particular<br /> activity_id that week, thatit lists the count as 0 and lists the<br /> activity_id associated like this:<br /><br /> count | activity_id<br />-------+---------------------<br /> 1502 | 1<br /> 11 | 2<br /> 2 | 3<br /> 815 | 4<br /> 0 | 5<br /> 0 | 6<br /> 4331 | 7<br /> 0 | 8<br /> 30 | 9<br /> 1950 | 10<br /> 7 | 11<br /> 67 | 12<br /><br /> Thanking you in advance for anyhelp on this. The caffiene seems to be not<br /> working well today.<br /><br /> Aaron<br /><font color="#888888"><br/><br /> --<br /> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br /></font></blockquote></div><br/><br clear="all" /><br />-- <br />Peter Steinheuser<br /><a href="mailto:psteinheuser@myyearbook.com">psteinheuser@myyearbook.com</a><br/>
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.
Assuming you have a table which lists all possible activities, with one activity per row and no duplicates, you need to do a left outer join between activities and your query result. That will generate a resultset that has at least one row for every row in activities, with nulls in all the columns coming from the query for rows that don't have a match. Then use coalesce to turn null into 0. Something like this:
select a.activity_id, coalesce(q.total, 0) as total
from activities a left outer join
(select fa.activity_id, count(fa.activity_id) as total from foo_activity fa
where fa.created between '01/01/2011' and '01/08/2011'
group by 1) q on a.activity_id = q.activity_id
order by a.activity_id
If you don't have an activities table with one row per activity, just replace the activities table in that query with another query - select distinct activity_id from foo_activity
On Fri, Feb 11, 2011 at 10:46 AM, Aaron Burnett <aburnett@bzzagent.com> 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
2011/2/11, Aaron Burnett <aburnett@bzzagent.com>: > > 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. > Try: SELECT sum(case when created >= '2011-01-01' and created < '2011-01-08' then 1 else 0 end), activity_id FROM foo_activity GROUP BY 2 ORDER BY 2; Osvaldo
<font face="Calibri, Verdana, Helvetica, Arial"><span style="font-size:11pt"><br /><br /> Thank you all very much for yourhelp.<br /><br /> The suggestion from Osvaldo below was the best for my situation (not having any soret of xref tableto join)...<br /><br /> Best Regards,<br /><br /> Aaron<br /><br /><br /> On 2/11/11 1:09 PM, "Osvaldo Kussama" <<ahref="osvaldo.kussama@gmail.com">osvaldo.kussama@gmail.com</a>> wrote:<br /><br /></span></font><blockquote><fontface="Calibri, Verdana, Helvetica, Arial"><span style="font-size:11pt">2011/2/11, AaronBurnett <<a href="aburnett@bzzagent.com">aburnett@bzzagent.com</a>>:<br /> ><br /> > Hi,<br /> ><br />> I'm just drawing a blank entirely today and would appreciate some help on<br /> > this.<br /> ><br /> > Thelong and short; there are 12 distinct activities that need to be queried<br /> > on a weekly basis:<br /> ><br />> SELECT count(activity_id), activity_id<br /> > FROM foo_activity<br /> > WHERE created >= '01/01/2011' andcreated < '01/08/2011'<br /> > GROUP BY 2<br /> > ORDER BY 2;<br /> ><br /> > It gives me this answer,which is correct:<br /> ><br /> > count | activity_id<br /> > -------+---------------------<br /> > 1502| 1<br /> > 11 | 2<br /> > 2 | 3<br /> > 815| 4<br /> > 4331 | 7<br /> > 30 | 9<br /> > 1950| 10<br /> > 7 | 11<br /> > 67 | 12<br /> ><br/> > But what I need to see is if there are no activities for the particular<br /> > activity_id that week,that it lists the count as 0 and lists the<br /> > activity_id associated like this:<br /> ><br /> > count| activity_id<br /> > -------+---------------------<br /> > 1502 | 1<br /> > 11 | 2<br /> > 2 | 3<br /> > 815 | 4<br /> > 0 | 5<br /> > 0 | 6<br /> > 4331 | 7<br /> > 0 | 8<br /> > 30 | 9<br /> > 1950 | 10<br /> > 7 | 11<br /> > 67 | 12<br /> ><br /> > Thanking you in advance for any help onthis. The caffiene seems to be not<br /> > working well today.<br /> ><br /><br /><br /> Try:<br /> SELECT sum(casewhen created >= '2011-01-01' and created <<br /> '2011-01-08' then 1 else 0 end), activity_id<br /> FROM foo_activity<br/> GROUP BY 2<br /> ORDER BY 2;<br /><br /> Osvaldo<br /><br /></span></font></blockquote>
On Fri, Feb 11, 2011 at 12:47 PM, Aaron Burnett <aburnett@bzzagent.com> wrote:
Thank you all very much for your help.
The suggestion from Osvaldo below was the best for my situation (not having any soret of xref table to join)...
It may work well for now, but if that foo_activity table has the potential to get large with only a relatively small percentage of rows fitting in the date range, it will get very slow compared to the left join, as it requires loading every row in the table, instead of being able to use an index to pull just the rows from the date range and then join to the list of valid activities.
--sam