Обсуждение: Table Join (Maybe?)

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

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

I do not believe that a single "query" will give you what you want here.  YOU could have 3
seperate queries:
1 for customer detail by state.
1 for state total 
1 for grand total

However, if you use a reporting tool like crystal or others, they have the ability to generate
summaries exactly as you are referring to here.  you would only need to pass it the query on total
per customer.  The reporting utility has a groupby feature where it would group the customers by
state for your.  In the group by summary it would automaticly display state total.  Then if the
report footer you could add a grand total summary for all of the records you passed to the report.

The following link show some of the reporting programs that you can use.
http://www.postgresql.org/community/survey.43 

Regards,
Richard Broersma Jr.

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)