[SQL] Table Join (Maybe?)

[Phillip Smith]

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   Table Defs: CREATE TABLE sales_figures (   rep_date date NOT NULL,   store varchar(6) NOT NULL,   sales_value numeric DEFAULT 0,   sales_customers int4 DEFAULT 0,   CONSTRAINT sales_figures_pkey PRIMARY KEY (rep_date, store),   CONSTRAINT sales_figures_store FOREIGN KEY (store)       REFERENCES stores (code) MATCH SIMPLE       ON UPDATE NO ACTION ON DELETE NO ACTION )   CREATE TABLE stores (   code varchar(2) NOT NULL DEFAULT ''::character varying,   name varchar(32) NOT NULL DEFAULT ''::character varying,   bms varchar(1) DEFAULT 'Y'::character varying,   state text DEFAULT 'UNKNOWN'::text,   business_open varchar(1) DEFAULT 'Y'::character varying,   CONSTRAINT stores_pkey PRIMARY KEY (code) )     Phillip Smith IT Coordinator Weatherbeeta P/L 8 Moncrief Rd Nunawading, VIC, 3131 AUSTRALIA   *******************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