From: Erik Jones <erik@myemma.com>
To: Phillip Smith <phillips@weatherbeeta.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Table Join (Maybe?)
Date: Wed, 19 Jul 2006 09:29:18 -0500
Message-ID: <44BE41BE.2000902@myemma.com> (raw)
In-Reply-To: <015b01c6ab05$d77f7b00$9b0014ac@ITPhil>
References: <015b01c6ab05$d77f7b00$9b0014ac@ITPhil>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: erik@myemma.com, phillips@weatherbeeta.com.au
Subject: Re: Table Join (Maybe?)
In-Reply-To: <44BE41BE.2000902@myemma.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox