12952

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

  • jquery limit lines in a paragraph and apply three periods to the end
  • Linq to SQL left outer join using Lambda syntax and joining on 2 columns (composite join key)
  • DirectX game with no prerequisite software to run
  • NHibernate Lazy Loading Behaviour
  • Comma within fields in CSV file -import to DB using SSIS
  • Net-ssh session timeout
  • ASP.NET - Efficiently delay code execution for single request?
  • Alfresco - Get username in workflow
  • CHAR vs VARCHAR for password security
  • How can I sum two different columns at once where one contains Decimal objects in pandas?
  • How to develop MS CRM kind of application
  • How to do JOIN ON query using Criteria API
  • MySql - get days remaining
  • How do I create an image and save it for later to draw as texture in XNA?
  • Returning this from a constructor function in JS
  • Paypal 'Buy Now' button not letting me dynamically set the price
  • Mockery and Laravel constructor injection
  • How to access recipient on sent messages page with mailboxer
  • Insertion large number of Entities into SQL Server 2012 [duplicate]
  • Unix Network Programming Clarification
  • What is the use of a session store?
  • How to get the date of next specified day of week
  • Time out Error in send mail
  • Django model for a Postgres view
  • ApplePay PKPaymentAuthorizationViewController always shows processing
  • Looking for good analogy/examples for monitor verses semaphore
  • Insert new calendar with SyncAdapter- Calendar API Android
  • Django: Count of Group Elements
  • Linq Objects Group By & Sum
  • Read text file and split every line in MSBuild
  • angularjs unit test when to use $rootScope.$new()
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • Weird JavaScript statement, what does it mean?
  • What do the 'size' numbers mean in the windbg !heap output?
  • Unanticipated behavior
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • embed rChart in Markdown
  • Can't mass-assign protected attributes when import data from csv file
  • Unable to use reactive element in my shiny app