Skip site navigation (1) Skip section navigation (2)

extracting min date and grouping

From: "Keith Worthington" <keithw(at)narrowpathinc(dot)com>
To: "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: extracting min date and grouping
Date: 2004-12-22 03:37:56
Message-ID: 20041222033756.M2892@narrowpathinc.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi All,

I have a table with a bunch of measurement data that I need to summarize.  I
would like to use the min date for my output along with a sum of the
quantites.  At some point this will all be written into a target table.

This is the data.
IPADB=# SELECT * FROM inventory.tbl_scanner;
   scan_timestamp    | item_id | quantity | employee_id | void 
---------------------+---------+----------+-------------+------
 2004-12-20 16:09:47 | SEB12   |      555 | 116         | f
 2004-12-20 16:10:03 | B346.0  |      555 | 116         | f
 2004-12-20 16:10:11 | B346.5  |      888 | 116         | f
 2004-12-20 16:09:33 | SAC38   |       66 | 116         | f
 2004-12-19 09:05:29 | SNAP50  |     2255 | 116         | f
 2004-12-19 09:05:39 | RSN2222 |      525 | 116         | f
 2004-12-19 09:05:49 | SAC38   |      658 | 116         | f
(7 rows)

I can get the min date but isn't there an easier/faster way?
IPADB=# SELECT CAST(extract(year from min(scan_timestamp)) || extract(month
from min(scan_timestamp)) || extract(day from min(scan_timestamp)) AS date) AS
inv_date FROM inventory.tbl_scanner;
  inv_date  
------------
 2004-12-19
(1 row)

The group and the sum is straightforward but I get this
IPADB=# SELECT CAST(extract(year from min(scan_timestamp)) || extract(month
from min(scan_timestamp)) || extract(day from min(scan_timestamp)) AS date) AS
inventory_date, item_id, sum(quantity) as total FROM inventory.tbl_scanner
GROUP BY item_id;
 inventory_date | item_id | total 
----------------+---------+-------
 2004-12-20     | B346.0  |   555
 2004-12-20     | B346.5  |   888
 2004-12-19     | RSN2222 |   525
 2004-12-19     | SAC38   |   724
 2004-12-20     | SEB12   |   555
 2004-12-19     | SNAP50  |  2255
(6 rows)

When what I really want is this.
 inventory_date | item_id | total 
----------------+---------+-------
 2004-12-19     | B346.0  |   555
 2004-12-19     | B346.5  |   888
 2004-12-19     | RSN2222 |   525
 2004-12-19     | SAC38   |   724
 2004-12-19     | SEB12   |   555
 2004-12-19     | SNAP50  |  2255

Any help is appreciated.

Kind Regards,
Keith

______________________________________________
99main Internet Services http://www.99main.com


Responses

pgsql-novice by date

Next:From: Mike G.Date: 2004-12-22 03:49:50
Subject: Re: syntax error in function
Previous:From: Jeffrey MelloyDate: 2004-12-21 21:55:20
Subject: Re: How to get day of week?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group