CODEWITHSUNDEEP
mysql
Jacobm001
Jacobm001

Reputation: 4539

Adding up sub sums in MYSQL

I'm working on a mySQL query that will hopefully add up sub totals of each table.

I have one table for members. This has their name, a unique ID and several other unrelated items. The other table is meant to keep track of their logged community service. Each member can be in it multiple times. The fields are uniqueID (foreign key to members), an auto_increment (for it's primary), term, number of hours and location.

What I want this query to do is add a sum of each member's hours for a given term. What I have is below, but unfortunately it only gives me the first member, with the total of each member. Any ideas?

SELECT fName, lName, SUM(hours)
FROM members M, communityService S
WHERE S.term = 'f2012' && M.onid = S.member

Upvotes: 1

Views: 85

Answers (2)

Marc B
Marc B

Reputation: 360702

You need a group by clause, e.g.

SELECT fName, lName, SUM(hours)
FROM members
INNER JOIN communityService ON communityService.member = members.onid
WHERE communityService.term = 'f2012'
GROUP BY fName, lName   <--- modify as desired for your requirements.

note that I've rewritten it using the modern join syntax. Nothing "wrong" with your server, but it's best to leave the "lazy" join syntax in the garbage head.

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270637

You are just missing a GROUP BY clause to aggregate the sum per fName,lName:

SELECT fName, lName, SUM(hours)
FROM members M, communityService S
WHERE S.term = 'f2012' && M.onid = S.member
GROUP BY lName, fName

Most RDBMS other than MySQL would have reported a syntax error on your query, but MySQL has a unique default behavior of selecting, non-deterministcally, a matching row to fill out the rest of the columns in your aggregate that don't appear in the GROUP BY clause. That's why you get the first row's lName, fName.

Review the documentation on aggregate functions and the GROUP BY clause.

It is recommended to use an explicit INNER JOIN instead of the implicit (comma-separated FROM).

SELECT
  fName,
  lName,
  SUM(hours) AS total_hours
FROM 
  members M, 
  INNER JOIN communityService S M.onid = S.member
WHERE 
  S.term = 'f2012' 
GROUP BY lName, fName

Upvotes: 1

Related Questions