How do I sum one column within a grouping of another column?

This is my problem:

NAME    DATE           PRESENTS
bob     march 3 2011      1
bob     june 6 2008       2
bob     jan 3 2012        3
mary    feb 14 1986       4
mary    april 10 2001     5
mary    jan 3 2012        6
kate    march 3 2011      7
kate    jan 3 2012        8
kate    oct 9 2013        9
celia   march 3 2011     10
celia   feb 14 1986      11
celia   july 4 2011      12
celia   jan 3 2012       13
celia   feb 14 1991      14

So the goal is that we add the amount of presents Kate and Celia got on the same days if they received any at all on those days.

What I need to do is kind of like this, except wit开发者_开发技巧h a much bigger data set. The size of the data set is probably around 100,000 entries.

I need the answer in SQL, or as an Access 2003 query.

If presents is the number of presents kate or celia or somepne else got on a particular date:

select name, date, sum(presents) total_on_date
from table
where name in ('kate','celia')
group by name, date

_{and I by no means am an sql genius}

select name, date, count(presents) total_on_date
from table
where name in ('kate','celia')
group by name, date

assuming you want to count the presents ids

You have to join back to itself, like this:

select DATE, SUM(PRESENTS) as TOTAL_JOINT_PRESENTS
from mytable t1
join mytable t2 on t1.DATE = t2.DATE
where t1.NAME = 'kate'
and t2.NAME = 'celia';

related to answer given by Bohemian :-

I think in question he wants to calculate the days in which these both person came...

select count(*)
from mytable t1 join mytable t2 
     on t1.DATE = t2.DATE 
where t1.NAME = 'kate' and t2.NAME = 'celia';

If you want

the amount of presents kate and celia got on the same days

then:

SELECT
    presents1.date AS presents_date,
    (COUNT(*) + 1) AS presents_count
FROM
    presents presents1,
    presents presents2
WHERE presents1.date = presents2.date
AND presents1.name = 'kate'
AND presents2.name = 'celia'
GROUP BY presents1.date

Asuming the table name is presents.