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 <[EMAIL PROTECTED]>
software development
emma(r)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
