> 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. ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
