Table Join (Maybe?) - Mailing list pgsql-sql

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hi again all,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I have two tables:</span></font><p class="MsoNormal" style="text-indent:36.0pt"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family:Arial">1. Sales figures by date and customer.</span></font><p
class="MsoNormal"style="text-indent:36.0pt"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">2.Customer details – including their </span></font><font face="Arial"
size="2"><spanstyle="font-size: 10.0pt;font-family:Arial">Geographic</span></font><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"></span></font><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">State</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">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,
andhave a grand total at the bottom.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Eg:</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">Customer 1  State 1     $100.00</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">Customer 2  State 1     $100.00</span></font><p class="MsoNormal"
style="margin-left:36.0pt;text-indent:36.0pt"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">State 1     $200.00</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">Customer 3  State 2     $100.00</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">Customer 4  State 2     $100.00</span></font><p class="MsoNormal"
style="margin-left:36.0pt;text-indent:36.0pt"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">State 2     $200.00</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">Grand Total             $400.00</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Does anyone have any magic pointers for me? I’ve been playing with SELECT INTO as 2 queries (the
individualcustomers, then the summary figures added to the temp table) but I end up with ROWS IN FIRST QUERY * ROWS IN
SECONDQUERY instead of them all sorted together nicely </span></font><font face="Wingdings" size="2"><span
style="font-size:10.0pt;font-family:Wingdings">L</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Thanks all,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">-p</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Table Defs:</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">CREATE TABLE sales_figures</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">(</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">  rep_date date NOT NULL,</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">  store varchar(6) NOT NULL,</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">  sales_value numeric DEFAULT 0,</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">  sales_customers int4 DEFAULT 0,</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">  CONSTRAINT sales_figures_pkey PRIMARY KEY (rep_date, store),</span></font><p
class="MsoNormal"><fontface="Courier New" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">  CONSTRAINT sales_figures_store FOREIGN KEY (store)</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">      REFERENCES stores (code) MATCH SIMPLE</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">      ON UPDATE NO ACTION ON DELETE NO ACTION</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">)</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New""> </span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">CREATE TABLE stores</span></font><p class="MsoNormal"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">(</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">  code varchar(2) NOT NULL DEFAULT ''::character varying,</span></font><p
class="MsoNormal"><fontface="Courier New" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">  name varchar(32) NOT NULL DEFAULT ''::character varying,</span></font><p
class="MsoNormal"><fontface="Courier New" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">  bms varchar(1) DEFAULT 'Y'::character varying,</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">  state text DEFAULT 'UNKNOWN'::text,</span></font><p class="MsoNormal"><font face="Courier
New"size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">  business_open varchar(1) DEFAULT 'Y'::character varying,</span></font><p
class="MsoNormal"><fontface="Courier New" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">  CONSTRAINT stores_pkey PRIMARY KEY (code)</span></font><p class="MsoNormal"><font
face="CourierNew" size="2"><span style="font-size:10.0pt; 
font-family:"Courier New"">)</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Phillip Smith<br /> IT Coordinator<br /> Weatherbeeta P/L<br /></span></font><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family: Arial">8 Moncrief Rd</span></font><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"><br/> Nunawading, VIC, 3131<br /></span></font><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family: Arial">AUSTRALIA</span></font><p class="MsoNormal"><font face="Times
NewRoman" size="3"><span style="font-size: 
12.0pt"> </span></font></div><br /><p><b>*******************Confidentiality and Privilege
Notice*******************</b><p>The material contained in this message is privileged and confidential to the addressee.
Ifyou are not the addressee indicated in this message or responsible for delivery of the message to such person, you
maynot copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.
<p>Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither
givennor 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 <br />