2

I have a view pulling together data from a number of tables. Let's consider it to be like this:

 TransPK   Amount    Currency_ID
 -------------------------------
 1         2000      0
 1         -2000     0
 2         3600      1
 2         -7200     2
 . . .

I want to calculate sum(Amount), per distinct TransPK, but only where all the Currency_IDs for a given TransPK are the same. i.e. in the data above there would only be one result returned, since for TransPK=2 there are two values for Currency_ID.

I have no idea what to search on for this! I've tried "SQL SUM restricted", but the hits weren't useful. I'm not the first person to want to do something like this. Is anyone able to point me in the right direction?

skiaddict1
  • 523
  • 7
  • 19

3 Answers3

1

Try this: 

SELECT TransPK, SUM(Amount)
FROM tableA 
GROUP BY TransPK
HAVING COUNT(DISTINCT Currency_ID) = 1;

Check the SQL FIDDLE DEMO

OUTPUT

| TRANSPK | AMOUNT |
|---------|--------|
|       1 |      0 |
|       3 |   3000 |
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
  • SQL requires me to add Currency_ID to the GROUP BY clause, and I'm still getting results for TransPK=2 :-( Darn, this one looked so promising!!! – skiaddict1 Dec 16 '14 at 07:39
0

I guess this is what you are after:

SELECT TransPK, SUM(Amount) AS Amount, Currency_ID
FROM TableName
GROUP BY TransPK, Currency_ID
HAVING COUNT(Currency_ID) > 1

Result:

TRANSPK AMOUNT  CURRENCY_ID
1       0       0

See result in SQL Fiddle.

Raging Bull
  • 18,593
  • 13
  • 50
  • 55
  • I think this won't work in general with my data. Transactions can have any number of entries (>= 2) in this view. Won't this restrict to ones that only have 2 entries? – skiaddict1 Dec 16 '14 at 07:40
  • @skiaddict1: Did you try my updated answer? `(Currency_ID)>1`. – Raging Bull Dec 16 '14 at 07:41
0

You'll need something like this

SELECT TransPK, Currency_ID, SUM(Amount)
FROM Your_Table
GROUP BY TransPK, Currency_ID

For more info about group by with multiple columns, have a look at Using group by on multiple columns

Community
  • 1
  • 1
CerebralFart
  • 3,336
  • 5
  • 26
  • 29