|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
SlickEdit: Code in over 40 languages across 7 platforms. SlickEdit’s unmatched power, speed, and flexibility allows even the most accomplished developers to write better code faster. Download a free trial today! |
|
#1
March 17th, 2008, 10:59 AM
|
|||
|
|||
|
Probably simple SQL
I am decent at simple SQL queries but this one is giving me a hard time, I'm sure someone else will figure this out in 2 seconds flat.
I have a clients table with clientid, then I have a transactions table with co-responding clientid. I want to do a search for clients who have the most paid. The tricky part is many clients have more than one transaction, so how do I make a query that adds up all of the clients specific paid amounts? For example, I have done something like this so far: SELECT DISTINCT clients.clientid, transactions.amountPaid FROM clients, transactions WHERE transactions.amountPaid > $volume AND clients.clientid = transactions.clientid ORDER by transactions.amountPaid LIMIT 1000 This can give me all the clients who have a transaction over a certain amount, then I have this SQL: SELECT SUM(amountPaid) as totalpaid FROM transactions WHERE clientid = $row[clientid] Which calculated and puts the total amount in my table I create with the data. The problem is if I search for clients over 50,000 paid for example, if they have two transactions for 30,000 it will not find them. My current method also doesn't allow me to sort by total amount paid for clients. Any one have any good ideas/suggestions? Thanks in advance! Aaron
|
|
#2
March 17th, 2008, 11:16 AM
|
|||
|
|||
|
Code:
select client,
sum(paidAmount) as total
from clients,
transactions
where clients.clientid = transactions.clientid
group by client
having sum(paidAmount) > $volume
order by total desc
limit 1000
|
|
#4
March 17th, 2008, 12:48 PM
|
|||
|
|||
|
One more quick question...
On every result from the first select query, I run another query to get the name of the salesmen. SELECT clients.firstName, clients.lastName, clients.country, sales.name FROM clients, sales WHERE clients.clientid = $row[clientid] AND clients.salesid = sales.salesid I'm sure I could integrate this into the query that was just made above, just not sure how to go about it without screwing it up! Can anyone quickly put it together for me? Thanks again so much Aaron
|
|
#5
March 17th, 2008, 01:09 PM
|
||||
|
||||
|
Should be something like
Code:
select
c.firstName,
c.lastName,
c.country,
s.name,
sum(t.paidAmount) as total
from
clients c inner join transactions t
on c.clientid = t.clientid
inner join sales s
on c.salesid = s.salesid
group by
c.firstName,
c.lastName,
c.country,
s.name
having
sum(t.paidAmount) > $volume
order by
total desc
limit 1000
__________________
My blog about OpenSource Databases PDF tutorials about OSS databases, DBMonster ... Please contribute to Open Source Development, fill bug reports!!! Developer Shed eSupport Commented my.ini/my.cnf (PLEASE ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins Last edited by pabloj : March 17th, 2008 at 01:14 PM.
|
|
| Viewing: Dev Shed Forums > Databases > Database Management > Probably simple SQL |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
