pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
group by date_part
6+ messages / 5 participants
[nested] [flat]

* group by date_part
@ 2000-02-20 23:33 M.Mazurek@poznan.multinet.pl
  2000-02-21 01:05 ` Re: [SQL] group by date_part Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: M.Mazurek@poznan.multinet.pl @ 2000-02-20 23:33 UTC (permalink / raw)
  To: pgsql-sql

HI,
I have a table with some statistics and datetime field (data). I want
select counted records by day of the week.

select count (*) from stats group by date_part('dow',data::datetime_;

I get everthing sortded just as I wanted to, but how can I ad another
column to know which dow it is?
eg:
dow|count
---------
0  |123
4  | 322
...

mazek





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] group by date_part
  2000-02-20 23:33 group by date_part M.Mazurek@poznan.multinet.pl
@ 2000-02-21 01:05 ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-02-21 02:07   ` Re: [SQL] group by date_part M.Mazurek@poznan.multinet.pl
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2000-02-21 01:05 UTC (permalink / raw)
  To: M.Mazurek@poznan.multinet.pl; +Cc: pgsql-sql

M.Mazurek@poznan.multinet.pl writes:
> select count (*) from stats group by date_part('dow',data::datetime_;

> I get everthing sortded just as I wanted to, but how can I ad another
> column to know which dow it is?

select date_part('dow',data::datetime), count(*) from stats
	group by date_part('dow',data::datetime);

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] group by date_part
  2000-02-20 23:33 group by date_part M.Mazurek@poznan.multinet.pl
  2000-02-21 01:05 ` Re: [SQL] group by date_part Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-02-21 02:07   ` M.Mazurek@poznan.multinet.pl
  0 siblings, 0 replies; 6+ messages in thread

From: M.Mazurek@poznan.multinet.pl @ 2000-02-21 02:07 UTC (permalink / raw)
  To: ; +Cc: pgsql-sql

On Sun, 20 Feb 2000, Tom Lane wrote:
> select date_part('dow',data::datetime), count(*) from stats
> 	group by date_part('dow',data::datetime);

thx, i think i got the idea:)

mazek




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Group by date_part
@ 2001-07-10 19:04 Graham Vickrage <graham@digitalplanit.com>
  2001-07-10 19:36 ` Re: Group by date_part Roberto Mello <rmello@cc.usu.edu>
  0 siblings, 1 reply; 6+ messages in thread

From: Graham Vickrage @ 2001-07-10 19:04 UTC (permalink / raw)
  To: pgsql-sql

Hi,

I need to select the amount of orders per day from an order table.

The statement I have only selects the count if there is at least 1 order for
a particular day, which make sense.

I however need a count of 0 for days that don't have any. Can anyone help?

SQL:

SELECT date_part('day', date), count(*)
FROM client_order WHERE (date >= '01/05/01' AND date < '01/06/01') AND
status = 'Processing'
GROUP BY date_part('day', date);




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Group by date_part
  2001-07-10 19:04 Group by date_part Graham Vickrage <graham@digitalplanit.com>
@ 2001-07-10 19:36 ` Roberto Mello <rmello@cc.usu.edu>
  2001-07-11 08:57   ` Re: Group by date_part Richard Huxton <dev@archonet.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Roberto Mello @ 2001-07-10 19:36 UTC (permalink / raw)
  To: Graham Vickrage <graham@digitalplanit.com>; +Cc: pgsql-sql

On Tue, Jul 10, 2001 at 08:04:55PM +0100, Graham Vickrage wrote:
> 
> The statement I have only selects the count if there is at least 1 order for
> a particular day, which make sense.
> 
> I however need a count of 0 for days that don't have any. Can anyone help?
> 
> SQL:
> 
> SELECT date_part('day', date), count(*)
> FROM client_order WHERE (date >= '01/05/01' AND date < '01/06/01') AND
> status = 'Processing'
> GROUP BY date_part('day', date);

	Didn't look very hard, but can't you just add a: 

CASE WHEN count(*) > 0 THEN count(*) ELSE 0 END AS count

	-Roberto
-- 
+----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+
  Roberto Mello - Computer Science, USU - http://www.brasileiro.net 
       http://www.sdl.usu.edu - Space Dynamics Lab, Developer    
HELP! My hard drive crashed & I can't boot up!



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Group by date_part
  2001-07-10 19:04 Group by date_part Graham Vickrage <graham@digitalplanit.com>
  2001-07-10 19:36 ` Re: Group by date_part Roberto Mello <rmello@cc.usu.edu>
@ 2001-07-11 08:57   ` Richard Huxton <dev@archonet.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Richard Huxton @ 2001-07-11 08:57 UTC (permalink / raw)
  To: Roberto Mello <rmello@cc.usu.edu>; Graham Vickrage <graham@digitalplanit.com>; +Cc: pgsql-sql

From: "Roberto Mello" <rmello@cc.usu.edu>

> On Tue, Jul 10, 2001 at 08:04:55PM +0100, Graham Vickrage wrote:
> >
> > The statement I have only selects the count if there is at least 1 order
for
> > a particular day, which make sense.
> >
> > I however need a count of 0 for days that don't have any. Can anyone
help?
> >
> > SQL:
> >
> > SELECT date_part('day', date), count(*)
> > FROM client_order WHERE (date >= '01/05/01' AND date < '01/06/01') AND
> > status = 'Processing'
> > GROUP BY date_part('day', date);
>
> Didn't look very hard, but can't you just add a:
>
> CASE WHEN count(*) > 0 THEN count(*) ELSE 0 END AS count

Don't think so - this one always catches me out, and I have to figure out
how to solve it each time. The problem is that if there aren't any records
with (eg) date = 2001-05-16 then there is simply nothing to return.

I've only ever come up with two solutions:

1. Create a temporary table with all the dates required and a total field
initialized to zero. Update the totals as desired then just read from the
table.

2. Create a temporary table with the dates and join against it. Then you can
use a CASE construct as above to get a zero.

Which one I select depends on usage patterns. If the dates don't change much
(e.g. weekending dates) then I'll use #2, otherwise #1.

I'd be very interested in any clever way of doing this without a temporary
table (or equivalent - if functions could return sets of values you could
use that, but it's basically the same thing).

- Richard Huxton




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2001-07-11 08:57 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-02-20 23:33 group by date_part M.Mazurek@poznan.multinet.pl
2000-02-21 01:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-02-21 02:07   ` M.Mazurek@poznan.multinet.pl
2001-07-10 19:04 Group by date_part Graham Vickrage <graham@digitalplanit.com>
2001-07-10 19:36 ` Re: Group by date_part Roberto Mello <rmello@cc.usu.edu>
2001-07-11 08:57   ` Re: Group by date_part Richard Huxton <dev@archonet.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