CODEWITHSUNDEEP
sqlsql-server
racinjasin
racinjasin

Reputation: 1052

SQL multiple joins and sums on same table

I'm learning SQL on the fly as I work on a project and would appreciate some help with the following. I'm also fairly new to stackoverflow so I apologize if my formatting is off:

I have a table with columns Date, Group, Person, Amount. For every day I have an entry for each person with an amount and the group they're in, so one row would look like:

  Date    Group   Person   Amount
8/7/2012     A     Steve     10

I'm trying to write a statement that will return the sum of all groups for two different days.

I have:

Select t1.group,sum(t1.amount),sum(t2.amount)
  From table t1, table t2
 Where t1.group=t2.group AND t1.date=current_date-1 AND t2.date=current_date-2
 Group by t1.group

I'm not getting any errors but the two sums are different from what I get if I just do

Select date,sum(amount) From table Group by date

and look at the days in question.

Upvotes: 3

Views: 1797

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Why are you joining between two tables?

I think you want:

Select t.group,
       sum(case when t.date = current_date - 1 then t.amount end),
       sum(case when t.date = current_date - 2 then t.amount end)
From table t
Group by t.group

Upvotes: 4

Related Questions