Friday, March 30, 2012

Need help bracketing counts

I need to create a view that shows the number of times that clients made payments, how many clients, and how much they paid over a period of time. I'm not sure if I can use a case for this. How can I put something like this together?

I have a tblClients with a clientid field

I have a tblPayments with the clientid, pmtdate, and pmtamount

For example:

1 Payment -- 23 Clients -- $16000

2 Payments -- 12 Clients -- $32000

3 Payments -- 4 Clients -- $13000

etc...

If you the the "how many clients" value you must also have such a field in the tblPayments table, or is it one purchased client per payment? If so, then

SELECT c.clientid, count(p.clientid) as clientCount,sum(p.pmtamount) as pmtSum
FROM tblClients c
LEFT JOIN tblPayments p ON c.clientid=p.clientid
WHERE p.pmtdate BETWEEN @.startdate AND @.enddate
GROUP BY c.clientid

|||

SELECTCOUNT(t.clientid)as clients,t.tblPaymentsCount,SUM(t.pmtamount)as pmtamountFROM(SELECT clientidas clientid,COUNT(tblPaymentsID)as tblPaymentsCount,SUM(pmtamount)as pmtamountFROM tblPaymentsGROUP BY clientid) tGROUP BY tblPaymentsCountORDER BY tblPaymentsCount
|||

Now that I saw BradFMJ's suggestion I realize that I quite possibly misunderstood the task

Maybe this is what you're after:

SELECTcount(*)as paymentCount,count(DISTINCT clientid)as clientCount,SUM(pmtamount)as pmtSumWHERE pmtdateBETWEEN @.startDateAND @.endDate

No comments:

Post a Comment