From: Michael A Nachbaur <mike@nachbaur.com>
To: "Ali Adams" <aliadams@doit4u.com>, <pgsql-sql@postgresql.org>
Subject: Re: help yourself by helping others
Date: Thu, 10 Jul 2003 13:00:43 -0700
User-Agent: KMail/1.5
References: <001c01c2fa9b$5b91ec80$03000000@DELLPC>
In-Reply-To: <001c01c2fa9b$5b91ec80$03000000@DELLPC>
MIME-Version: 1.0
Content-Type: Multipart/Mixed;
  boundary="Boundary-00=_rXcD/t96Z9Pdc7t"
Message-Id: <200307101300.43414.mike@nachbaur.com>

--Boundary-00=_rXcD/t96Z9Pdc7t
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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."

--Boundary-00=_rXcD/t96Z9Pdc7t
Content-Type: text/x-sql;
  charset="iso-8859-1";
  name="pgsql-example.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename="pgsql-example.sql"

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;

--Boundary-00=_rXcD/t96Z9Pdc7t--