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
