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.

Answer1:

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

人吐槽 人点赞

Recommend

Comment

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:SQL multiple joins and sums on same table