CODEWITHSUNDEEP
sqldelphi
Beast
Beast

Reputation: 135

Sum of column for another Group By column in delphi

I'm working on a simple delphi form to create a daily balance sheet therefore i'm using a DBGrid having its columns from a table and containing: date, in, out..what I want to do is group by date and calculate the In and Out for each date in order to make the balance.

Example: 

     1/01/2013 in (100) out (0),
     1/01/2013 in (200) out (0),
     1/01/2013 in (0) out (100),

I want the result to be 1/01/2013 in (300) out (100)

Any help Please cause I'm having trouble after grouping the dates.

Upvotes: 0

Views: 2109

Answers (1)

Ken White
Ken White

Reputation: 125728

You use the aggregate expression (SUM) more than once:

SELECT 
  YourDateField, 
  Sum(in) as InAmount, 
  Sum(Out) as OutAmount,
  Sum(In) - Sum(Out) as Balance
FROM 
  YourTable
GROUP BY
   YourDateField

Output:

YourDateField    InAmount    OutAmount
=============    ========    =========
01/01/2013       300         100

Replace YourDateField and YourTable with the name of your date column and actual table, of course, and the column aliases (InAmount, OutAmount, and Balance) to whatever you'd like.

Upvotes: 3

Related Questions