All,

I have always wondered if there is an easier way to do this. Lets say I have a Customer table and an Order table... Now, I have a need of a query that will get me the total order amount for each customer.

The simple sql

SELECT sum(amount) FROM orders GROUP BY custid

Ok, that is nice, but I want to see the customers id... so I write

SELECT custid, sum(amount) 
FROM orders o
JOIN customers c ON o.custid=c.custid
GROUP BY c.custid

Ok, so far so good.. but, now I want all the info from the customer so that I can put this on a report, I want name, address, etc, etc from customers... so I write my query...

SELECT c.*, sum(amount)
FROM orders o
JOIN customers c ON o.custid=c.custit
GROUP BY c.custid

Looks good, but the above query gives me errors, something about you can't select columns unless you use an aggregate or put it in the group.

So, I know I can do this...

SELECT c.*, sum(amount)
FROM orders o
JOIN customers c ON o.custid=c.custit
GROUP BY c.*

The above seems to work, but if there is a text field it chokes on it. But, is there a better way? Is a correlated subquery as a column perhaps the best way...

SELECT c.*, (select sum(amount) from orders where custid=c.custid)
FROM customers c

I would love to hear how you guys handle this. Of course, it gets much more hary when there are several joins and you want sums and maxes from various tables in the join and you want all the identifying data too.

BOb