From: Michael A Nachbaur <mike@nachbaur.com>
To: Ali Adams <aliadams@doit4u.com>
To: pgsql-sql@postgresql.org
Subject: Re: help yourself by helping others
Date: Thu, 10 Jul 2003 13:00:43 -0700
Message-ID: <200307101300.43414.mike@nachbaur.com> (raw)
In-Reply-To: <001c01c2fa9b$5b91ec80$03000000@DELLPC>
References: <001c01c2fa9b$5b91ec80$03000000@DELLPC>
On Friday 04 April 2003 03:10 am, Ali Adams wrote:
> Dear All,
>
> I am new to Relational Databases and SQL and my background in ODBs is
> clouding my way to solving what seems to be a simple problem. I am sure
> many of you have met it many times.
<snip>
Assuming you can have multiple records per day per machine (e.g. you're
polling for information throughout the day), the following holds. However,
the output doesn't provide for a separate column per day, but rather you get
a separate row for each day/machine combination.
Attached is a file that demonstrates the behavior I think you would like. But
basically, here is the SQL statement you're looking for:
SELECT Datestamp, Machine, SUM(Withdrawls)
FROM WithdrawlsTable
GROUP BY Machine, Datestamp
ORDER BY Datestamp, Machine;
--
/* Michael A. Nachbaur <mike@nachbaur.com>
* http://nachbaur.com/pgpkey.asc
*/
"A thought seemed to strike the woman. It struck her very slowly. You could
watch it coming in like a long wave on a sandy beach."
Attachments:pgsql-example.sql (text/x-sql, 2.0K) download
CREATE TABLE WithdrawlsTable
( id integer
, machine integer
, datestamp date
, withdrawls integer
);
INSERT INTO WithdrawlsTable (id, machine, datestamp, withdrawls) VALUES(1, 1, '01-01-2003', 1101);
INSERT INTO WithdrawlsTable (id, machine, datestamp, withdrawls) VALUES(2, 2, '01-01-2003', 2101);
INSERT INTO WithdrawlsTable (id, machine, datestamp, withdrawls) VALUES(3, 3, '01-01-2003', 3101);
INSERT INTO WithdrawlsTable (id, machine, datestamp, withdrawls) VALUES(4, 1, '02-01-2003', 1102);
INSERT INTO WithdrawlsTable (id, machine, datestamp, withdrawls) VALUES(5, 2, '02-01-2003', 2102);
INSERT INTO WithdrawlsTable (id, machine, datestamp, withdrawls) VALUES(6, 1, '03-01-2003', 1103);
INSERT INTO WithdrawlsTable (id, machine, datestamp, withdrawls) VALUES(7, 3, '03-01-2003', 3103);
INSERT INTO WithdrawlsTable (id, machine, datestamp, withdrawls) VALUES(8, 2, '04-01-2003', 2104);
INSERT INTO WithdrawlsTable (id, machine, datestamp, withdrawls) VALUES(9, 4, '04-01-2003', 4104);
INSERT INTO WithdrawlsTable (id, machine, datestamp, withdrawls) VALUES(11, 1, '01-01-2003', 1101);
INSERT INTO WithdrawlsTable (id, machine, datestamp, withdrawls) VALUES(12, 2, '01-01-2003', 2101);
INSERT INTO WithdrawlsTable (id, machine, datestamp, withdrawls) VALUES(13, 3, '01-01-2003', 3101);
INSERT INTO WithdrawlsTable (id, machine, datestamp, withdrawls) VALUES(14, 1, '02-01-2003', 1102);
INSERT INTO WithdrawlsTable (id, machine, datestamp, withdrawls) VALUES(15, 2, '02-01-2003', 2102);
INSERT INTO WithdrawlsTable (id, machine, datestamp, withdrawls) VALUES(16, 1, '03-01-2003', 1103);
INSERT INTO WithdrawlsTable (id, machine, datestamp, withdrawls) VALUES(17, 3, '03-01-2003', 3103);
INSERT INTO WithdrawlsTable (id, machine, datestamp, withdrawls) VALUES(18, 2, '04-01-2003', 2104);
INSERT INTO WithdrawlsTable (id, machine, datestamp, withdrawls) VALUES(19, 4, '04-01-2003', 4104);
SELECT Datestamp
, Machine
, SUM(Withdrawls)
FROM WithdrawlsTable
GROUP
BY Machine
, Datestamp
ORDER
BY Datestamp
, Machine;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@nachbaur.com, aliadams@doit4u.com
Subject: Re: help yourself by helping others
In-Reply-To: <200307101300.43414.mike@nachbaur.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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