SELECT sum(amount) FROM orders GROUP BY custidOk, 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.custidOk, 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.custidLooks 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.
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 cI 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.