pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDrawing a blank on some SQL
7+ messages / 5 participants
[nested] [flat]
* Drawing a blank on some SQL
@ 2011-02-11 18:46 Aaron Burnett <aburnett@bzzagent.com>
0 siblings, 4 replies; 7+ messages in thread
From: Aaron Burnett @ 2011-02-11 18:46 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Drawing a blank on some SQL
@ 2011-02-11 19:11 Peter Steinheuser <psteinheuser@myyearbook.com>
parent: Aaron Burnett <aburnett@bzzagent.com>
3 siblings, 0 replies; 7+ messages in thread
From: Peter Steinheuser @ 2011-02-11 19:11 UTC (permalink / raw)
To: Aaron Burnett <aburnett@bzzagent.com>; +Cc: pgsql-sql
Not tested.
1. select count(t2.activity_id),
t1.activity_id
from (select distinct activity_id from foo_activity) as t1, -- assumes
all activities exist somewhere in table
left join foo_activity t2 on (t1.activity_id = t2.activity_id)
WHERE created >= '01/01/2011' and created < '01/08/2011'
group by 2
order by 2;
2. -- use generate_series for simple numbering scheme
select count(t2.activity_id)
t1.x as 'activity_id"
from generate_series(1,12) as t1(x),
left join foo_activity t2 on (t1.x = t2.activity_id)
WHERE created >= '01/01/2011' and created < '01/08/2011'
group by 2
order by 2;
On Fri, Feb 11, 2011 at 1:46 PM, 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
>
--
Peter Steinheuser
psteinheuser@myyearbook.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Drawing a blank on some SQL
@ 2011-02-11 19:17 Rob Sargent <robjsargent@gmail.com>
parent: Aaron Burnett <aburnett@bzzagent.com>
3 siblings, 0 replies; 7+ messages in thread
From: Rob Sargent @ 2011-02-11 19:17 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Drawing a blank on some SQL
@ 2011-02-11 19:22 Samuel Gendler <sgendler@ideasculptor.com>
parent: Aaron Burnett <aburnett@bzzagent.com>
3 siblings, 0 replies; 7+ messages in thread
From: Samuel Gendler @ 2011-02-11 19:22 UTC (permalink / raw)
To: Aaron Burnett <aburnett@bzzagent.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Drawing a blank on some SQL
@ 2011-02-11 20:09 Osvaldo Kussama <osvaldo.kussama@gmail.com>
parent: Aaron Burnett <aburnett@bzzagent.com>
3 siblings, 1 reply; 7+ messages in thread
From: Osvaldo Kussama @ 2011-02-11 20:09 UTC (permalink / raw)
To: Aaron Burnett <aburnett@bzzagent.com>; +Cc: 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Drawing a blank on some SQL
@ 2011-02-11 20:47 Aaron Burnett <aburnett@bzzagent.com>
parent: Osvaldo Kussama <osvaldo.kussama@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Aaron Burnett @ 2011-02-11 20:47 UTC (permalink / raw)
To: pgsql-sql
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)...
Best Regards,
Aaron
On 2/11/11 1:09 PM, "Osvaldo Kussama" <osvaldo.kussama@gmail.com> wrote:
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Drawing a blank on some SQL
@ 2011-02-12 00:55 Samuel Gendler <sgendler@ideasculptor.com>
parent: Aaron Burnett <aburnett@bzzagent.com>
0 siblings, 0 replies; 7+ messages in thread
From: Samuel Gendler @ 2011-02-12 00:55 UTC (permalink / raw)
To: Aaron Burnett <aburnett@bzzagent.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2011-02-12 00:55 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-02-11 18:46 Drawing a blank on some SQL Aaron Burnett <aburnett@bzzagent.com>
2011-02-11 19:11 ` Peter Steinheuser <psteinheuser@myyearbook.com>
2011-02-11 19:17 ` Rob Sargent <robjsargent@gmail.com>
2011-02-11 19:22 ` Samuel Gendler <sgendler@ideasculptor.com>
2011-02-11 20:09 ` Osvaldo Kussama <osvaldo.kussama@gmail.com>
2011-02-11 20:47 ` Aaron Burnett <aburnett@bzzagent.com>
2011-02-12 00:55 ` Samuel Gendler <sgendler@ideasculptor.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox