CODEWITHSUNDEEP
mysqljoinsum
RiCHiE
RiCHiE

Reputation: 288

MYSQL Join SUM with a WHERE clause

Sorry if this has been asked before but I just can't figure out a solution or find anything on it anywhere.

Basically what I need to do is display only amounts over 300 for a join that adds up a total amount from registrations with matching IDs

TABLES: table registration table attendees

In simpler table terms, TABLE A and TABLE B I need to get B.Amount total by joining WHERE A.ID and B.ID match and summing B.Amount where the B.ID is a repeat only. This is what I used to total up registrations paid by grouping payments by B.ID:

SELECT tblattendees.FirstName, tblattendees.LastName, 
SUM(tblregistration.RegistrationPaid) AS 'TotalPaid'
FROM tblattendees
JOIN tblregistration
ON tblregistration.RegistrationID = tblattendees.AttendeeID 
GROUP BY tblregistration.AttendeeID

This works fine till you want to limit the results to over 300

I have tried adding WHERE after the join and using AND with the ON but it seems to mess with the adding up probably due to my use of the GROUP BY to get a total of RegistrationsPaid.

Maybe some thing I'm not understanding in this, any help would be greatly appreciated.

Upvotes: 1

Views: 2435

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175746

You can use subquery and use condition in WHERE clause:

SELECT *
FROM (
  SELECT tblattendees.FirstName, tblattendees.LastName, 
  SUM(tblregistration.RegistrationPaid) AS `TotalPaid`
  FROM tblattendees
  JOIN tblregistration
    ON tblregistration.RegistrationID = tblattendees.AttendeeID 
  GROUP BY tblregistration.AttendeeID
) AS sub
WHERE sub.TotalPaid > 300

Or you can use HAVING as proposed in comment:

SELECT tblattendees.FirstName, tblattendees.LastName, 
SUM(tblregistration.RegistrationPaid) AS `TotalPaid`
FROM tblattendees
JOIN tblregistration
  ON tblregistration.RegistrationID = tblattendees.AttendeeID 
GROUP BY tblregistration.AttendeeID
HAVING SUM(tblregistration.RegistrationPaid) > 300

In MySQL you should use alias directly:

SELECT tblattendees.FirstName, tblattendees.LastName, 
SUM(tblregistration.RegistrationPaid) AS `TotalPaid`
FROM tblattendees
JOIN tblregistration
  ON tblregistration.RegistrationID = tblattendees.AttendeeID 
GROUP BY tblregistration.AttendeeID
HAVING `TotalPaid` > 300;

See also simplified order of execution:

enter image description here

Image source: http://social.technet.microsoft.com/wiki/contents/articles/20724.all-at-once-operations-in-t-sql.aspx

Upvotes: 1

Related Questions