Need an SQL Query - ready to pay!

[handsonwebhosting]

Hi guys,

 

I've just been informed by our accountant that he needs to have a listing of all customers in the state of California and also how much they have paid for the year 2010 so far. This will come into play for us at the end of the year for taxes due to some new law.

 

Anyway, I'm looking to get a query that will output all clients that were active during 2010 that are located in the state of California and how much they spent. Ideally it would be nice to see how much each individual spent, but a lump sum total would be fine too.

 

Anyone up for a challenge? <<removed>>

[othellotech]

you'll need to define what you mean by *active* - had an account during that period ? an invoice ? a transaction (payment) ?

[laszlof]

SELECT SUM( i.total ) 
FROM tblinvoices i, tblclients c
WHERE i.status = 'Paid'
AND i.datepaid LIKE  '2010-%'
AND (
c.state LIKE 'cali%'
OR c.state = 'CA'
)
AND c.id = i.userid

 

 

EDIT: heres the code if you want to get it per userid:

 

SELECT c.id, SUM( i.total ) AS total
FROM tblinvoices i, tblclients c
WHERE i.status = 'Paid'
AND i.datepaid LIKE '2010-%'
AND (
c.state LIKE 'cali%'
OR c.state = 'CA'
)
AND c.id = i.userid
GROUP BY c.id

Edited October 7, 2010 by laszlof