Question about SUM query

  • Thread starter Thread starter Cirene
  • Start date Start date
C

Cirene

I have a "transaction" table with 2 columns, TransTypeId and TransAmt.
Example:

TransTypeId TransAmt
A 100
A 200
B 100
C 50
B 100
A 100

I am trying to do a SUM of all the TransTypeId A and B. The result should
be 600. I WANT TO GET ONLY THIS 1 VALUE BACK.

But, the result of the query is returning...
A 400
B 200

It's not summing it as a "group", but per "TransTypeId". Any ideas why?

Here's the raw query... (
SELECT SUM(Transactions.TransAmt) AS Expr1, Enroll.ProgramId,
Enroll.EnrollId
FROM Transactions INNER JOIN
Enroll ON Transactions.EnrollId = Enroll.EnrollId
GROUP BY Enroll.ProgramId, Enroll.EnrollId, Transactions.TransTypeId
HAVING (Enroll.ProgramId = @ProgramId) AND (Enroll.EnrollId =
@EnrollId) AND (Transactions.TransTypeId = 1 OR
Transactions.TransTypeId = 2 OR
Transactions.TransTypeId = 3)

Thanks!
 
I think this select would do the trick:
Select SUM(TransAmt) FROM Transactions where TransTypeId IN ('A', 'B')
 
Thanks Miha...

Miha Markic said:
I think this select would do the trick:
Select SUM(TransAmt) FROM Transactions where TransTypeId IN ('A', 'B')

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Cirene said:
I have a "transaction" table with 2 columns, TransTypeId and TransAmt.
Example:

TransTypeId TransAmt
A 100
A 200
B 100
C 50
B 100
A 100

I am trying to do a SUM of all the TransTypeId A and B. The result
should be 600. I WANT TO GET ONLY THIS 1 VALUE BACK.

But, the result of the query is returning...
A 400
B 200

It's not summing it as a "group", but per "TransTypeId". Any ideas why?

Here's the raw query... (
SELECT SUM(Transactions.TransAmt) AS Expr1, Enroll.ProgramId,
Enroll.EnrollId
FROM Transactions INNER JOIN
Enroll ON Transactions.EnrollId = Enroll.EnrollId
GROUP BY Enroll.ProgramId, Enroll.EnrollId, Transactions.TransTypeId
HAVING (Enroll.ProgramId = @ProgramId) AND (Enroll.EnrollId =
@EnrollId) AND (Transactions.TransTypeId = 1 OR
Transactions.TransTypeId = 2 OR
Transactions.TransTypeId = 3)

Thanks!
Click to expand...
Click to expand...
 
Back
Top