Mysql query help with where clause

Question

Community Service Table

|student name (id in real table)|hours|year|event name (id in real table)|
|Johnny Smith                   |    5|2010|Beach Clean-up               |
|Samantha Bee                   |    3|2011|Daily Show Volunteering      |
|Samantha Bee                   |    2|2011|Daily Show Bake Sale         |
|Bilbo Baggins                  |   10|2011|The Shire Feast Setup        |

From this table I'd like the following output:

Hours in 2011:

Johnny Smith:   0 (his hours are in 2010)
Samantha Bee:   5
Bilbo Baggins: 10

Here's my failed attempt:

mysql_query("select name,
                    sum(hours) 
                    from community_service 
                    where year = '2011' 
                    or year is null 
                    group by name"); 

I understand why 'year is null' doesn't give me what I want, I just don't know how to do it correctly.

Thanks in advance!

Since my simplied query seems to be confusing people, here's the whole enchilada:

SELECT DISTINCT contacts.contactID, 
sum(hours) as hours, 
contacts.first, 
contacts.last, 
contacts.graduates 
from contacts 
left join comm_stud using (contactID) 
left join comm_svc using (csID) 
left join tbl_yeardiv on comm_svc.termID = tbl_yeardiv.yeardivID 
WHERE (year = '2010-11') 
group by contacts.contactID 

tbl_yeardiv contains school terms like Fall and Spring semesters. comm_stud joins students to each comm_svc event.

Answer 1

Don't you want to group by name?

If you want zero values to appear as well, join the table against itself:

SELECT a.name, sum(b.hours)
from community_service a
LEFT OUTER JOIN
community_service b
on a.name = b.name
WHERE b.year = 2011
GROUP BY a.name

If in your real applications you have multiple tables, you just LEFT OUTER JOIN the usertable against the ID-HOUR-YEAR table.

Answer 2

try isnull(years). also, you don't need sum if you don't have more than one entry per student and year (if you do, you can only use sum sensibly if you also group by name, year). Finally, why is year a string, ie why do you put apostrophes around it?

If none of these help, please update your post with a specification of what exactly doesn't work.

Answer 3

You don't need sum(hours), hours will be fine.

select name,
       hours 
from community_service 
where year = '2011'

Answer 4

The solution is actually pretty easy. It involves adding a condition to the ON clause. From: http://opensourceanalytics.com/2006/07/29/advanced-mysql-join-tips-tricks/

Now suppose you want to search not from the entire table2, but from a subset of table2 (say CityID = 1). How do you do that? You can try and see for yourself that the following SQL is not the solution:

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL
and table2.CityID = 1

Note that this is because we are looking for records that do not exist in table2 and hence cannot have a where clause on table2 data.

Read the manual page a little further, and another user comment points out:

Conditions for the “right table” go in the ON clause.
Conditions for the “left table” go in the WHERE clause,
except for the joining conditions themselves.

That makes the solution as:

SELECT table1.* FROM table1 LEFT JOIN table2 ON (table1.id=table2.id and table2.CityID = 1)
WHERE table2.id IS NULL

So, the solution to my original query looks something like this: (tested working query)

SELECT DISTINCT contacts.contactID, 
sum(hours) as hours, 
contacts.first, 
contacts.last, 
contacts.graduates from contacts
left join comm_stud using (contactID)
left join comm_svc using (csID)

(This is the secret sauce:)

left join tbl_yeardiv on (comm_svc.termID = tbl_yeardiv.yeardivID && year = '".thisyear()."')

left join group_members on contacts.contactID = group_members.contactID
left join groups using (groupID)
WHERE groups.name = 'Students' 
and group_members.status like '%Matriculated%'
group by contacts.contactID 

Thanks for all your help, everyone. I hope what I found helps you write queries more easily.