|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
August 9th, 2005, 11:52 AM
|
|||
|
|||
|
sql newbee needs help with query
(I posted this under the "Microsoft SQL Server" section but then I saw that there was a section for "MySQL" - which is more appropriate because that is what I am using. Sorry for any inconvenience)
Hi, I'm new to sql and am working on a contact management program. One of the functionalities of this program is that the user can make queries based on user defined criteria (kind of like Lotus Approach). I need to incorporate the following into the criteria that users can select. I have two database tables: Contact Donations Contact contains our contacts (people). Donations contains any donations those people may have given. So, say Joe Blow (contact) has given 5 donations in the past year, $5, $50, $10, $100, $25 -- the total being $190. Each donation is a separate record and the field that contains the amount for each donation is called "donation_amount". Now, I need to be able to query with the total donation amount of one contact as one of the criteria. For example: The user of this program wants to know all the contacts who have given a total of at least $150 in the past year. How do I do that? I know how to do everything except the total of donations. And not only do I have to be able to do it, it has to be dynamic somehow. Can anyone help me with this? If I need to explain more, let me know. Thanks, Daniel Lorimer
|
|
#2
August 9th, 2005, 01:31 PM
|
|||
|
|||
|
For the total donations, use SUM():
Code:
SELECT c.name ,SUM(d.donation_amount) as total_donations FROM contacts c INNER JOIN donations d ON c.id = d.contact_id GROUP BY c.name HAVING total_donations > 150 For the dynamic part, that depends on what language you're using to build the front-end.
__________________
"A pawn is the most important piece on the chessboard -- to a pawn"
|
|
#3
August 9th, 2005, 04:39 PM
|
|||
|
|||
|
Thank you! That works great!
I've never used a HAVING clause before. Now I have another question. Say, I want to find the total donations ONLY for the past 6 months. I have a field that has the date the donation was made. I can get the total donations for only contacts who have given in the past 6 months, but I can't figure out how to get the total donation amount for ONLY a certain period of time. I set the date constraint by putting it in the WHERE clause: WHERE d.donation_actual_date > 2005-1-1 but that still gives the total for all the times, not just since Jan. Can you please help me? Thanks, Daniel Lorimer
|
|
| Viewing: Dev Articles Community Forums > Databases > MySQL Development > sql newbee needs help with query |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
