X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id CF60C9FA4AA for ; Wed, 19 Jul 2006 20:52:53 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 04320-08 for ; Wed, 19 Jul 2006 23:52:46 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from weatherbeeta.com.au (mail.weatherbeeta.com.au [202.154.127.242]) by postgresql.org (Postfix) with SMTP id 0389D9FA4A7 for ; Wed, 19 Jul 2006 20:52:44 -0300 (ADT) Received: (qmail 16657 invoked from network); 19 Jul 2006 23:52:37 -0000 X-NetworkBox-HamSign: 0101;OUT;wbeetaau1;9fb301e4c5b1ef73790e85c4f2f2e1a1; Received: from mail.weatherbeeta.com.au (HELO wb-email) (172.20.0.2)by network-box.weatherbeeta.com.au with SMTP; 19 Jul 2006 23:52:36 -0000 Received: from it-phil.weatherbeeta.com.au ([172.20.0.155]) by wb-email (602Pro LAN SUITE 2003) id 2d570304 for pgsql-sql@postgresql.org; Thu, 20 Jul 2006 9:50:37 +1000 From: "Phillip Smith" To: Subject: FW: Table Join (Maybe?) Date: Thu, 20 Jul 2006 09:51:53 +1000 Message-ID: <003801c6ab8e$50610880$9b0014ac@ITPhil> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0039_01C6ABE2.220D1880" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 Importance: Normal X-Scanned-By-wbeetaau1: Virus scan performed by network-box X-Scanned-By-wbeetaau1: Scanner file id is wbeetaau1-1153353156.663-16645-000 X-Scanned-By-wbeetaau1: No known viruses found in message (received+scanned in 0.06/0.15 secs) X-Scanned-By-wbeetaau1: Spam-Check-Result: No, hits=0 required=7 tests= autolearn=no version=2.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/256 X-Sequence-Number: 25830 This is a multi-part message in MIME format. ------=_NextPart_000_0039_01C6ABE2.220D1880 Content-Type: text/plain;charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Thanks all for your suggestions - the below suggestion works perfectly. =20 A little tweak of the column counts and group by clauses makes it work = like a charm. =20 Richard - this particular extract is via PHP to a web page so I can't = use those reporting tools, but I'll have a look and fiddle for my next = project - Thanks! =20 Cheers, -p =20 -----Original Message----- From: Arulmani V A [mailto:arulmani_s@yahoo.com]=20 Sent: Wednesday, 19 July 2006 20:49 To: Phillip Smith Subject: Re: [SQL] Table Join (Maybe?) =20 Hi, Postgres does not seem to support OLAP functions like ROLLUP, CUBE, etc. = I'm not a Postgres expert, but I think we can try the following alternative approach : Sample data for the two tables given by you are created as : Table : stores code name bms state business_open 1 Customer1 Y State1 Y 2 Customer2 Y State2 Y 3 Customer3 Y State1 Y 4 Customer4 Y State2 Y Table : sales_figures rep_date store sales_value sales_customers 2006-01-01 1 1000 0 2006-01-02 1 1000 0 2006-02-01 2 200 0 2006-02-02 2 200 0 2006-02-03 2 500 0 2006-01-03 1 100 0 2006-01-01 3 300 0 2006-01-02 3 200 0 2006-02-01 4 700 0 2006-02-02 4 400 0 If I execute the following query : SELECT a.name, a.state, SUM(b.sales_value) FROM stores a, sales_figures = b WHERE a.code =3D b.store GROUP BY a.state, a.name UNION SELECT NULL, a.state, SUM(b.sales_value) FROM stores a, sales_figures b WHERE a.code =3D b.store GROUP BY a.state UNION SELECT 'GRAND TOTAL', NULL, SUM(b.sales_value) FROM stores a, = sales_figures b WHERE a.code =3D=20 b.store ORDER BY 2 I get the following output (same as what you expect?) : name state sum Customer1 State1 2100 Customer3 State1 500 NULL State1 2600 Customer2 State2 900 Customer4 State2 1100 NULL State2 2000 GRAND TOTAL NULL 4600 Is the above approach OK? Regards Arul ----- Original Message ---- From: Phillip Smith To: pgsql-sql@postgresql.org Sent: Wednesday, July 19, 2006 1:04:58 PM Subject: [SQL] Table Join (Maybe?) Hi again all, =20 I have two tables: 1. Sales figures by date and customer. 2. Customer details - including their Geographic State =20 I need to extract a report from the first table (I can do that!), and in that report order by their State (I can do that too!), but I also need a summary of all the customers in each state, below the end of each state, = and have a grand total at the bottom. =20 Eg: Customer 1 State 1 $100.00 Customer 2 State 1 $100.00 State 1 $200.00 Customer 3 State 2 $100.00 Customer 4 State 2 $100.00 State 2 $200.00 Grand Total $400.00 =20 Does anyone have any magic pointers for me? I've been playing with = SELECT INTO as 2 queries (the individual customers, then the summary figures = added to the temp table) but I end up with ROWS IN FIRST QUERY * ROWS IN = SECOND QUERY instead of them all sorted together nicely :-( =20 Thanks all, -p =20 Table Defs: CREATE TABLE sales_figures ( rep_date date NOT NULL, store varchar(6) NOT NULL, sales_value numeric DEFAULT 0, sales_customers int4 DEFAULT 0, CONSTRAINT sales_figures_pkey PRIMARY KEY (rep_date, store), CONSTRAINT sales_figures_store FOREIGN KEY (store) REFERENCES stores (code) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) =20 CREATE TABLE stores ( code varchar(2) NOT NULL DEFAULT ''::character varying, name varchar(32) NOT NULL DEFAULT ''::character varying, bms varchar(1) DEFAULT 'Y'::character varying, state text DEFAULT 'UNKNOWN'::text, business_open varchar(1) DEFAULT 'Y'::character varying, CONSTRAINT stores_pkey PRIMARY KEY (code) ) =20 =20 Phillip Smith IT Coordinator Weatherbeeta P/L 8 Moncrief Rd Nunawading, VIC, 3131 AUSTRALIA=20 =20 =20 *******************Confidentiality and Privilege = Notice*******************=20 The material contained in this message is privileged and confidential to = the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy = or deliver this message to anyone, and you should destroy it and kindly = notify the sender by reply email.=20 Information in this message that does not relate to the official = business of Weatherbeeta must be treated as neither given nor endorsed by = Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be = liable for direct, indirect or consequential loss arising from transmission of = this message or any attachments=20 =20 *******************Confidentiality and Privilege Notice******************* The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments ------=_NextPart_000_0039_01C6ABE2.220D1880 Content-Type: text/html;charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

Thanks all for your suggestions = – the below suggestion works perfectly.

 

A little tweak of the column counts = and group by clauses makes it work like a charm.

 

Richard – this particular = extract is via PHP to a web page so I can’t use those reporting tools, but = I’ll have a look and fiddle for my next project – = Thanks!

 

Cheers,

-p

 

-----Original Message-----
From: Arulmani V A [mailto:arulmani_s@yahoo.com]
Sent: Wednesday, 19 July 2006 20:49
To: Phillip Smith
Subject: Re: [SQL] Table = Join (Maybe?)

 

Hi,

Postgres does not seem to support OLAP functions like ROLLUP, CUBE, etc. = I'm not a Postgres expert, but I think we can try the following alternative approach :

Sample data for the two tables given by you are created as :

Table : stores
code     name         bms =     state         business_open
1        Customer1    Y        State1        Y
2        Customer2    Y        State2        Y
3        Customer3    Y        State1        Y
4        Customer4    Y        State2        Y

Table : sales_figures
rep_date     store     sales_value     sales_customers
2006-01-01    1        1000            0
2006-01-02    1        1000            0
2006-02-01    2        200            =     0
2006-02-02    2        200            =     0
2006-02-03    2        500            =     0
2006-01-03    1        100            =     0
2006-01-01    3        300            =     0
2006-01-02    3        200            =     0
2006-02-01    4        700            =     0
2006-02-02    4        400            =     0

If I execute the following query :
SELECT a.name, a.state, SUM(b.sales_value) FROM stores a, sales_figures = b WHERE a.code =3D b.store GROUP BY a.state, a.name
UNION
SELECT NULL, a.state, SUM(b.sales_value) FROM stores a, sales_figures b = WHERE a.code =3D b.store GROUP BY a.state
UNION
SELECT 'GRAND TOTAL', NULL, SUM(b.sales_value) FROM stores a, = sales_figures b WHERE a.code =3D
b.store ORDER BY 2

I get the following output (same as what you expect?) :

name        state    = sum

Customer1    State1    2100
Customer3    State1    500
NULL        State1    = 2600
Customer2    State2    900
Customer4    State2    1100
NULL        State2    = 2000
GRAND TOTAL    NULL    4600

Is the above approach OK?

Regards
Arul

----- Original = Message ----
From: Phillip Smith <phillips@weatherbeeta.com.au>
To: pgsql-sql@postgresql.org
Sent: Wednesday, July 19, 2006 1:04:58 PM
Subject: [SQL] Table Join (Maybe?)

Hi again all,

 

I have two tables:

1. Sales figures by date = and customer.

2. Customer details – including their Geographic State

 

I need to extract a report from the first table (I = can do that!), and in that report order by their State (I can do that too!), = but I also need a summary of all the customers in each state, below the end of = each state, and have a grand total at the bottom.

 

Eg:

Customer 1  = State 1     $100.00

Customer 2  = State 1     $100.00

State 1     = $200.00

Customer 3  = State 2     $100.00

Customer 4  = State 2     $100.00

State 2     = $200.00

Grand Total           &n= bsp; $400.00

 

Does anyone have any magic pointers for me? = I’ve been playing with SELECT INTO as 2 queries (the individual customers, then the = summary figures added to the temp table) but I end up with ROWS IN FIRST QUERY * = ROWS IN SECOND QUERY instead of them all sorted together nicely = L

 

Thanks all,

-p

 

Table Defs:

CREATE TABLE = sales_figures

(

  rep_date = date NOT NULL,

  store = varchar(6) NOT NULL,

  sales_value = numeric DEFAULT 0,

  = sales_customers int4 DEFAULT 0,

  CONSTRAINT sales_figures_pkey PRIMARY KEY (rep_date, = store),

  CONSTRAINT sales_figures_store FOREIGN KEY (store)

      REFERENCES stores (code) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION

)

 

CREATE TABLE = stores

(

  code = varchar(2) NOT NULL DEFAULT ''::character varying,

  name = varchar(32) NOT NULL DEFAULT ''::character varying,

  bms = varchar(1) DEFAULT 'Y'::character varying,

  state text = DEFAULT 'UNKNOWN'::text,

  = business_open varchar(1) DEFAULT 'Y'::character varying,

  CONSTRAINT stores_pkey PRIMARY KEY (code)

)

 

 

Phillip Smith
IT Coordinator
Weatherbeeta P/L
8 Moncrief Rd
Nunawading, VIC, 3131
AUSTRALIA

 

 

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy = or deliver this message to anyone, and you should destroy it and kindly = notify the sender by reply email.

Information in this message that does not relate to the official business of = Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. = Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message = or any attachments

 


*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.

Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments
------=_NextPart_000_0039_01C6ABE2.220D1880--