From aliadams@doit4u.com Mon Jul  7 11:14:43 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (developer.postgresql.org [64.117.224.193])
	by svr1.postgresql.org (Postfix) with ESMTP id 56E57B43965
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  7 Jul 2003 11:14:43 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new,
	port 10024) with ESMTP id 24395-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  7 Jul 2003 08:14:10 -0300 (ADT)
Received: from bahama.vosn.net (bahama.vosn.net [205.214.72.216])
	by svr1.postgresql.org (Postfix) with ESMTP id B66AAB430A6
	for <pgsql-sql@postgresql.org>; Mon,  7 Jul 2003 08:13:59 -0300 (ADT)
Received: from gr.189.18.84.dial.global.net.uk ([80.189.18.84] helo=DELLPC)
	by bahama.vosn.net with asmtp (Exim 3.36 #1) id 19ZTwC-0005PI-00
	for pgsql-sql@postgresql.org; Mon, 07 Jul 2003 05:13:41 -0600
Message-ID: <001c01c2fa9b$5b91ec80$03000000@DELLPC>
From: "Ali Adams" <aliadams@doit4u.com>
To: <pgsql-sql@postgresql.org>
Subject: help yourself by helping others
Date: Fri, 4 Apr 2003 12:10:06 +0100
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0008_01C2FAA3.2184A6B0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-AntiAbuse: This header was added to track abuse,
	please include it with any abuse report
X-AntiAbuse: Primary Hostname - bahama.vosn.net
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [0 0]
X-AntiAbuse: Sender Address Domain - doit4u.com
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/116
X-Sequence-Number: 14230

This is a multi-part message in MIME format.

------=_NextPart_000_0008_01C2FAA3.2184A6B0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Dear All,

I am new to Relational Databases and SQL and my background in ODBs is cloud=
ing my way to solving what seems to be a simple problem. I am sure many of =
you have met it many times.

OK, I have a table as follows:


ID Machine   Date             Withdrawals
1  1              01/01/2003    1101
2  2              01/01/2003    2101
3  3              01/01/2003    3101

4  1              02/01/2003    1102
5  2              02/01/2003    2102

6  1              03/01/2003    1103
7  3              03/01/2003    3103

8  2              04/01/2003    2104
9  4              04/01/2003    4104

And i would like to create a monthly withdrawals report as follows:

Machine   Day1  Day2  Day3  Day4 ..........................................=
.... Day31
1             11      12      13       0                                   =
                   0
2             21      22      0        24                                  =
                   0
3             31      0        33       0                                  =
                    0
4              0        0       0         0                                =
                     0

Can you please help?

Many thanks in advance.

Ali
<help yourself by helping others>
www.geocities.com/aliadams

------=_NextPart_000_0008_01C2FAA3.2184A6B0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1106" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Dear All,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I am new to Relational Databases and SQL a=
nd my=20
background in ODBs is clouding my way to solving what seems to be a simple=
=20
problem. I am sure many of you have met it many times.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>OK, I have a table as follows:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>ID Machine&nbsp;&nbsp;=20
Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;=20
Withdrawals</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>1&nbsp;=20
1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;=20
01/01/2003&nbsp;&nbsp;&nbsp; 1101</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>2&nbsp;=20
2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;=20
01/01/2003&nbsp;&nbsp;&nbsp; 2101</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>3&nbsp;=20
3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;=20
01/01/2003&nbsp;&nbsp;&nbsp; 3101</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>4&nbsp;=20
1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;=20
02/01/2003&nbsp;&nbsp;&nbsp; 1102</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>5&nbsp;=20
2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;=20
02/01/2003&nbsp;&nbsp;&nbsp; 2102</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>6&nbsp;=20
1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;=20
03/01/2003&nbsp;&nbsp;&nbsp; 1103</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>7&nbsp;=20
3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;=20
03/01/2003&nbsp;&nbsp;&nbsp; 3103</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>8&nbsp;=20
2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;=20
04/01/2003&nbsp;&nbsp;&nbsp; 2104</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>9&nbsp;=20
4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;=20
04/01/2003&nbsp;&nbsp;&nbsp; 4104</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>And i would like to create a monthly withd=
rawals=20
report as follows:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV>
<DIV><FONT face=3DArial size=3D2>Machine&nbsp;&nbsp; Day1&nbsp; Day2&nbsp;=
=20
Day3&nbsp; Day4 ..............................................=20
Day31</FONT></DIV>
<DIV><FONT face=3DArial=20
size=3D2>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0</FONT></DIV>
<DIV><FONT face=3DArial=20
size=3D2>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;21&nbsp;&nbsp;=20
&nbsp;&nbsp; 22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
24&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;=20
0</FONT></DIV>
<DIV><FONT face=3DArial=20
size=3D2>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;31&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
33&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;=20
0</FONT></DIV>
<DIV><FONT face=3DArial=20
size=3D2>4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;=20
0</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV></DIV>
<DIV><FONT face=3DArial size=3D2>Can you please help?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Many thanks&nbsp;in advance.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Ali</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&lt;help yourself by helping=20
others&gt;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2><A=20
href=3D"http://www.geocities.com/aliadams">www.geocities.com/aliadams</A></=
FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_0008_01C2FAA3.2184A6B0--




From dev@archonet.com Thu Jul  10 09:41:29 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.193])
	by svr1.postgresql.org (Postfix) with ESMTP id 0E24CCC542
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Jul 2003 09:41:29 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new,
	port 10024) with ESMTP id 87076-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Jul 2003 06:40:56 -0300 (ADT)
Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net
	[194.217.242.89])
	by svr1.postgresql.org (Postfix) with ESMTP id 77744CC21F
	for <pgsql-sql@postgresql.org>; Thu, 10 Jul 2003 05:24:24 -0300 (ADT)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-31.mail.demon.net with esmtp (Exim 3.35 #1)
	id 19aWjE-000AOX-0V; Thu, 10 Jul 2003 09:24:36 +0100
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mainbox.archonet.com (Postfix) with ESMTP
	id EBB7916D61; Thu, 10 Jul 2003 09:24:34 +0100 (BST)
Received: from client.archonet.com (client.archonet.com [192.168.1.16])
	by mainbox.archonet.com (Postfix) with ESMTP
	id 8CEBC16D5D; Thu, 10 Jul 2003 09:24:32 +0100 (BST)
Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: "Ali Adams" <aliadams@doit4u.com>, <pgsql-sql@postgresql.org>
Subject: Re: help yourself by helping others
Date: Thu, 10 Jul 2003 09:24:36 +0100
User-Agent: KMail/1.4.3
References: <001c01c2fa9b$5b91ec80$03000000@DELLPC>
In-Reply-To: <001c01c2fa9b$5b91ec80$03000000@DELLPC>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200307100924.37218.dev@archonet.com>
X-Virus-Scanned: by AMaViS snapshot-20020531
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/127
X-Sequence-Number: 14241

On Friday 04 Apr 2003 12:10 pm, 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.
>
> OK, I have a table as follows:
>
>
> ID Machine   Date             Withdrawals
> 1  1              01/01/2003    1101
> 2  2              01/01/2003    2101
> 3  3              01/01/2003    3101
[snip]
> And i would like to create a monthly withdrawals report as follows:
>
> Machine   Day1  Day2  Day3  Day4............ Day31

Two options - either do it in your app or write a crosstab function.

You can extract the day part of the month using:
  SELECT EXTRACT('day' FROM now());

You can find some crosstab functions in the contrib/tablefunc directory of =
the=20
source distribution (or possibly as a package if installed as binary)



--=20
  Richard Huxton



From ymalik@cs.stevens-tech.edu Thu Jul  10 23:12:55 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.193])
	by svr1.postgresql.org (Postfix) with ESMTP id 0BBF8CC376
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Jul 2003 23:12:55 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new,
	port 10024) with ESMTP id 02226-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Jul 2003 20:12:40 -0300 (ADT)
Received: from guinness.cs.stevens-tech.edu (guinness.cs.stevens-tech.edu
	[155.246.89.8])
	by svr1.postgresql.org (Postfix) with ESMTP id 23747CBFB4
	for <pgsql-sql@postgresql.org>; Thu, 10 Jul 2003 09:24:59 -0300 (ADT)
Received: by guinness.cs.stevens-tech.edu (Postfix, from userid 4405)
	id 70769141691; Thu, 10 Jul 2003 08:25:13 -0400 (EDT)
Received: from localhost (localhost [127.0.0.1])
	by guinness.cs.stevens-tech.edu (Postfix) with ESMTP
	id 6286C10A522; Thu, 10 Jul 2003 08:25:13 -0400 (EDT)
Date: Thu, 10 Jul 2003 08:25:13 -0400
From: Yasir Malik <ymalik@cs.stevens-tech.edu>
To: Ali Adams <aliadams@doit4u.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: help yourself by helping others
In-Reply-To: <001c01c2fa9b$5b91ec80$03000000@DELLPC>
Message-ID: <Pine.SGI.4.51.0307100819130.4280122@guinness.cs.stevens-tech.edu>
References: <001c01c2fa9b$5b91ec80$03000000@DELLPC>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/132
X-Sequence-Number: 14246

I'll try.
In your report, I do not understand how you are getting the withdrawls.
For example, in  your table, you have for machine 1, day one of January,
1101 withdrawls but that does not show in your report.  And what is the
relation of id to machine?
Yasir

On Fri, 4 Apr 2003, Ali Adams wrote:

> Date: Fri, 4 Apr 2003 12:10:06 +0100
> From: Ali Adams <aliadams@doit4u.com>
> To: pgsql-sql@postgresql.org
> Subject: [SQL] help yourself by helping others
>
> 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.
>
> OK, I have a table as follows:
>
>
> ID Machine   Date             Withdrawals
> 1  1              01/01/2003    1101
> 2  2              01/01/2003    2101
> 3  3              01/01/2003    3101
>
> 4  1              02/01/2003    1102
> 5  2              02/01/2003    2102
>
> 6  1              03/01/2003    1103
> 7  3              03/01/2003    3103
>
> 8  2              04/01/2003    2104
> 9  4              04/01/2003    4104
>
> And i would like to create a monthly withdrawals report as follows:
>
> Machine   Day1  Day2  Day3  Day4 .............................................. Day31
> 1             11      12      13       0                                                      0
> 2             21      22      0        24                                                     0
> 3             31      0        33       0                                                      0
> 4              0        0       0         0                                                     0
>
> Can you please help?
>
> Many thanks in advance.
>
> Ali
> <help yourself by helping others>
> www.geocities.com/aliadams
>



From rbt@rbt.ca Fri Jul  11 09:16:53 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.193])
	by svr1.postgresql.org (Postfix) with ESMTP id 5710AD38AD
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 11 Jul 2003 09:16:53 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new,
	port 10024) with ESMTP id 85796-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 11 Jul 2003 06:16:35 -0300 (ADT)
Received: from mailav.tor1.inquent.com (mail.inquent.com [216.208.117.106])
	by svr1.postgresql.org (Postfix) with ESMTP id ED58BCD3AE
	for <pgsql-sql@postgresql.org>; Thu, 10 Jul 2003 10:46:49 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by mailav.tor1.inquent.com (Postfix) with ESMTP
	id 6FCFF1024D; Thu, 10 Jul 2003 09:40:20 -0400 (EDT)
Received: from [10.0.2.7] (unknown [10.0.2.7])
	by mailav.tor1.inquent.com (Postfix) with ESMTP
	id 692CC10248; Thu, 10 Jul 2003 09:40:18 -0400 (EDT)
Subject: Re: help yourself by helping others
From: Rod Taylor <rbt@rbt.ca>
To: Ali Adams <aliadams@doit4u.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <001c01c2fa9b$5b91ec80$03000000@DELLPC>
References: <001c01c2fa9b$5b91ec80$03000000@DELLPC>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-bFYUxoltQDRufhFThQFp"
Message-Id: <1057844856.46100.134.camel@jester>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.0 
Date: 10 Jul 2003 13:47:37 +0000
X-Virus-Scanned: by AMaViS perl-11
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/141
X-Sequence-Number: 14255

--=-bFYUxoltQDRufhFThQFp
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Fri, 2003-04-04 at 11:10, Ali Adams wrote:
> Dear All,
>=20=20
> 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.

You need to use a pivot table.

Specifically, see crosstab() within contrib/tablefunc in the source
base.


--=-bFYUxoltQDRufhFThQFp
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA/DW536DETLow6vwwRAgnQAJ9X3Ds+D9qIvfOxHYdLMKzFaTtOZACggnoJ
Ii7xPLwzS049913kMiUhRFs=
=APPu
-----END PGP SIGNATURE-----

--=-bFYUxoltQDRufhFThQFp--




From scott.marlowe@ihs.com Thu Jul  10 19:32:45 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.193])
	by svr1.postgresql.org (Postfix) with ESMTP id B5D98CD7E1
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Jul 2003 19:32:45 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new,
	port 10024) with ESMTP id 78365-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Jul 2003 16:31:56 -0300 (ADT)
Received: from mail1.ihs.com (mail1.ihs.com [170.207.70.222])
	by svr1.postgresql.org (Postfix) with ESMTP id 08A7FCD83A
	for <pgsql-sql@postgresql.org>; Thu, 10 Jul 2003 12:28:34 -0300 (ADT)
Received: from css120.ihs.com (css120.ihs.com [170.207.105.120])
	by mail1.ihs.com (8.12.9/8.12.9) with ESMTP id h6AFRWE7002012;
	Thu, 10 Jul 2003 09:27:32 -0600 (MDT)
Date: Thu, 10 Jul 2003 09:20:43 -0600 (MDT)
From: "scott.marlowe" <scott.marlowe@ihs.com>
To: Ali Adams <aliadams@doit4u.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: help yourself by helping others
In-Reply-To: <001c01c2fa9b$5b91ec80$03000000@DELLPC>
Message-ID: <Pine.LNX.4.33.0307100919180.10734-100000@css120.ihs.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-MailScanner: Found to be clean
X-MailScanner-SpamCheck: 
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/130
X-Sequence-Number: 14244

On Fri, 4 Apr 2003, 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.
> 
> OK, I have a table as follows:
> 
> 
> ID Machine   Date             Withdrawals
> 1  1              01/01/2003    1101
> 2  2              01/01/2003    2101
> 3  3              01/01/2003    3101
> 
> 4  1              02/01/2003    1102
> 5  2              02/01/2003    2102
> 
> 6  1              03/01/2003    1103
> 7  3              03/01/2003    3103
> 
> 8  2              04/01/2003    2104
> 9  4              04/01/2003    4104
> 
> And i would like to create a monthly withdrawals report as follows:
> 
> Machine   Day1  Day2  Day3  Day4 .............................................. Day31
> 1             11      12      13       0                                                      0
> 2             21      22      0        24                                                     0
> 3             31      0        33       0                                                      0
> 4              0        0       0         0                                                     0
> 
> Can you please help?

What you need is a table that has all the dates you want a report on 
already in it to join against.  Just something like:

day
---
1
2
3
4
5
6
7
...
29
30
31  <- or fewer for other months.  

Just make a temp table to hold the days.  Then join against that using 
extract().

If you get stuck let us know.




From jgardner@jonathangardner.net Thu Jul  10 23:48:38 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.193])
	by svr1.postgresql.org (Postfix) with ESMTP id 058E6CE4DA
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Jul 2003 23:48:38 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new,
	port 10024) with ESMTP id 06746-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Jul 2003 20:48:11 -0300 (ADT)
Received: from irko.smoothcorp.com (unknown [208.49.241.41])
	by svr1.postgresql.org (Postfix) with ESMTP id 141C9CE692
	for <pgsql-sql@postgresql.org>; Thu, 10 Jul 2003 16:14:12 -0300 (ADT)
Received: from jgardner.ifloor.com ([10.0.1.63])
	by irko.smoothcorp.com with esmtp (Exim 4.12)
	id 19ags3-0007he-00; Thu, 10 Jul 2003 12:14:23 -0700
Received: from jgardner.ifloor.com (localhost.localdomain [127.0.0.1])
	by jgardner.ifloor.com (8.12.8/8.12.8) with ESMTP id h6AJEOHK019461;
	Thu, 10 Jul 2003 12:14:24 -0700
Received: from localhost (localhost [[UNIX: localhost]])
	by jgardner.ifloor.com (8.12.8/8.12.8/Submit) id h6AJEOOU019459;
	Thu, 10 Jul 2003 12:14:24 -0700
From: Jonathan Gardner <jgardner@jonathangardner.net>
To: "Ali Adams" <aliadams@doit4u.com>, <pgsql-sql@postgresql.org>
Subject: Re: help yourself by helping others
Date: Thu, 10 Jul 2003 12:14:22 -0700
User-Agent: KMail/1.5
References: <001c01c2fa9b$5b91ec80$03000000@DELLPC>
In-Reply-To: <001c01c2fa9b$5b91ec80$03000000@DELLPC>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Description: clearsigned data
Content-Disposition: inline
Message-Id: <200307101214.23951.jgardner@jonathangardner.net>
X-SA-Exim-Rcpt-To: aliadams@doit4u.com, pgsql-sql@postgresql.org
X-SA-Exim-Scanned: No; SAEximRunCond expanded to false
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/133
X-Sequence-Number: 14247

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Friday 04 April 2003 03:10, 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.
>
> OK, I have a table as follows:
>
>
> ID Machine   Date             Withdrawals
> 1  1              01/01/2003    1101
> 2  2              01/01/2003    2101
> 3  3              01/01/2003    3101
>
> 4  1              02/01/2003    1102
> 5  2              02/01/2003    2102
>
> 6  1              03/01/2003    1103
> 7  3              03/01/2003    3103
>
> 8  2              04/01/2003    2104
> 9  4              04/01/2003    4104
>
> And i would like to create a monthly withdrawals report as follows:
>
> Machine   Day1  Day2  Day3  Day4
> .............................................. Day31 1             11=20=
=20=20=20=20
> 12      13       0                                                      0=
 2
>             21      22      0        24=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
>                 0 3             31      0        33       0=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20
>                                      0 4              0        0       0=
=20=20=20
>      0                                                     0
>
> Can you please help?
>

Doing this purely in SQL is difficult. The reason is because there is no wa=
y=20
to say things like "all the dates in a month, eg, 7/1, 7/2, 7/3, ... 7/31" =
in=20
SQL.

What I would do is select the data using aggregates and "group by" the=20
machine. You can specify how many days or months to aggregate across by=20
"group by date_trunc("day", Date)" or "date_trunc("month", Date)" or whatev=
er=20
you like. You can get some interesting behavior by combining that with=20
date_part() (IE, grouping by day of the week, to compare Mondays with=20
Tuesdays.)

The data you get back will look something like this:

Machine | Date | Withdrawals
- ---
1 | June 1 | 50
1 | June 7 | 65
1 | June 8 | 12
2 | June 2 | 14
2 | June 9 | 19

When you go to display the code, you populate a list of dates and withdrawa=
ls=20
for each machine. You fill in '0' for the missing dates. Then displaying it=
=20
is easy. You can also probably iterate over the results if everything is=20
sorted, and fill in the missing dates as you encounter them.

- --=20
Jonathan Gardner <jgardner@jonathangardner.net>
(was jgardn@alumni.washington.edu)
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/DbsOWgwF3QvpWNwRAqUiAKCDxyDbzRrIqzLDcZ6jOMESLvooAwCgvlOU
HYWoH/6iRlqhgDB5/KaoYJA=3D
=3DrhPR
-----END PGP SIGNATURE-----



From mike@nachbaur.com Fri Jul  11 08:42:08 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.193])
	by svr1.postgresql.org (Postfix) with ESMTP id 0C431F20A8
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 11 Jul 2003 08:42:08 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new,
	port 10024) with ESMTP id 76064-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 11 Jul 2003 05:41:52 -0300 (ADT)
Received: from dbdevel.sunwave.net (h64-141-16-15.sunwave.net [64.141.16.15])
	by svr1.postgresql.org (Postfix) with ESMTP id D32A3CCB70
	for <pgsql-sql@postgresql.org>; Thu, 10 Jul 2003 17:00:28 -0300 (ADT)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by dbdevel.sunwave.net (Postfix) with ESMTP
	id 8C935178008; Thu, 10 Jul 2003 16:00:43 -0400 (EDT)
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>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/140
X-Sequence-Number: 14254

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




From mallah@trade-india.com Sun Jul  13 07:47:09 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.225.181])
	by svr1.postgresql.org (Postfix) with ESMTP id 6AE43CB8F9
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 13 Jul 2003 07:47:09 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new,
	port 10024) with ESMTP id 16575-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 13 Jul 2003 07:46:58 +0000 (GMT)
Received: from rtlocal.trade-india.com (mail-relay.trade-india.com
	[203.196.129.235])
	by svr1.postgresql.org (Postfix) with SMTP id B5FC0CB66E
	for <pgsql-sql@postgresql.org>; Sun, 13 Jul 2003 04:46:34 -0300 (ADT)
Received: (qmail 27246 invoked from network); 13 Jul 2003 07:54:45 -0000
Received: from unknown (HELO system67.trade-india-local.com) (unknown)
	by unknown with SMTP; 13 Jul 2003 07:54:45 -0000
From: Rajesh Kumar Mallah <mallah@trade-india.com>
Organization: Infocom Network Limited
To: "Ali Adams" <aliadams@doit4u.com>, <pgsql-sql@postgresql.org>
Subject: Re: help yourself by helping others
Date: Sun, 13 Jul 2003 13:16:49 +0530
User-Agent: KMail/1.5.1
References: <001c01c2fa9b$5b91ec80$03000000@DELLPC>
In-Reply-To: <001c01c2fa9b$5b91ec80$03000000@DELLPC>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200307131316.49667.mallah@trade-india.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/153
X-Sequence-Number: 14267

dear ali,

something like

select machine,date_part('day' , date) , sum(withdrawals) from 
Table where date_part('month' , date)='month in question' group by
machine,date_part('day' , date) ;

will give you agrregated withdrawals by machine and day,
use the frontend language  for formatting it.

note that it will not display the days for which there
has been no withdrawls. If u need to report them also 0
then create a table that holds 1 year of dates and 
left or right join the output of first query with it.
exact query is not being provided , its just an idea.



regds
mallah.


On Friday 04 Apr 2003 4:40 pm, 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.
>
> OK, I have a table as follows:
>
>
> ID Machine   Date             Withdrawals
> 1  1              01/01/2003    1101
> 2  2              01/01/2003    2101
> 3  3              01/01/2003    3101
>
> 4  1              02/01/2003    1102
> 5  2              02/01/2003    2102
>
> 6  1              03/01/2003    1103
> 7  3              03/01/2003    3103
>
> 8  2              04/01/2003    2104
> 9  4              04/01/2003    4104
>
> And i would like to create a monthly withdrawals report as follows:
>
> Machine   Day1  Day2  Day3  Day4
> .............................................. Day31 1             11     
> 12      13       0                                                      0 2
>             21      22      0        24                                    
>                 0 3             31      0        33       0                
>                                      0 4              0        0       0   
>      0                                                     0
>
> Can you please help?
>
> Many thanks in advance.
>
> Ali
> <help yourself by helping others>
> www.geocities.com/aliadams