MagiCat
04-09-2008, 05:49 PM
Not sure of the version of SQL, it's on an AS/400 system but it seems to be pretty standard (and what I'm doing seems like it would work on pretty much anything.)
I have a query which calculates the sum of all invoices for every customer for the month and also gives the full customer information.
SELECT invoiceaccount.01, SUM(charges.01) as totalcharges, name.02, adr1.02, adr2.02,
adr3.02 city.02, stat.02, post.02, ctry.02, tel.02, . . . 35 more fields from table 2
FROM invoices, customers
JOIN invoiceaccount.01=customeraccount.02
WHERE invoicedate is between 20080101 AND 20080131
GROUP BY account.01, name.02, adr1.02, adr2.02, adr3.02 city.02, stat.02,
post.02, ctry.02, tel.02, . . . same 35 more fields as above from table 2
HAVING totalcharges>=5.00
Which is working correctly. The only problem is that I really don't want to have more than 40 GROUP BY items when they're really not needed for the grouping (the only true group is the Account Number and the extras are always going to be the same for the given customer.) If I eliminate the GROUP BY stuff for everything in table 2 I get an error saying I need it.
Is there an alternative way to create this query so that I get the totals I need while also getting the extra fields from the customer table?
I have a query which calculates the sum of all invoices for every customer for the month and also gives the full customer information.
SELECT invoiceaccount.01, SUM(charges.01) as totalcharges, name.02, adr1.02, adr2.02,
adr3.02 city.02, stat.02, post.02, ctry.02, tel.02, . . . 35 more fields from table 2
FROM invoices, customers
JOIN invoiceaccount.01=customeraccount.02
WHERE invoicedate is between 20080101 AND 20080131
GROUP BY account.01, name.02, adr1.02, adr2.02, adr3.02 city.02, stat.02,
post.02, ctry.02, tel.02, . . . same 35 more fields as above from table 2
HAVING totalcharges>=5.00
Which is working correctly. The only problem is that I really don't want to have more than 40 GROUP BY items when they're really not needed for the grouping (the only true group is the Account Number and the extras are always going to be the same for the given customer.) If I eliminate the GROUP BY stuff for everything in table 2 I get an error saying I need it.
Is there an alternative way to create this query so that I get the totals I need while also getting the extra fields from the customer table?