Message-ID: <44BE41BE.2000902@myemma.com>
Date: Wed, 19 Jul 2006 09:29:18 -0500
From: Erik Jones <erik@myemma.com>
User-Agent: Thunderbird 1.5.0.4 (Windows/20060516)
MIME-Version: 1.0
To: Phillip Smith <phillips@weatherbeeta.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Table Join (Maybe?)
References: <015b01c6ab05$d77f7b00$9b0014ac@ITPhil>
In-Reply-To: <015b01c6ab05$d77f7b00$9b0014ac@ITPhil>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

Phillip Smith wrote:
>
> 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 $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
>
Well, two queries one for the individual totals and one for the summary 
totals is good (maybe a third for the grand total), but you should do a 
union of the two and then play with the order by and/or group by clauses 
(depending on the data) to get the ordering that you want. I can't even 
count the times I've spent banging my head against the proverbial wall 
(you do have a proverbial wall don't you?) trying to get these kinds of 
queries to work with joins, sub-queries, case statements, etc... only to 
come back to using union on simple, to-the-point queries.

-- 
erik jones <erik@myemma.com>
software development
emma(r)