Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lukeodonoghue
Starting Member
2 Posts |
 Posted - 2005-04-25 : 11:07:13
|
| I have a database of financial transactions. Each row in table 'TRANS' includes amongst other information, an 'Amount' and an accounts 'Nominal'.I would like to constuct a query that would give me the sum of the transactions for each accounts nominal and a breakdown between Debits and Credits. Debits are negative and Credits are positive in this case.I'm using SQL 2000 MSDE currently and programming through ADO 2.5 in Visual Basic 6. The following code should return just the sum of debits for each nominal but the query fails to execute:"SELECT Nominal, SUM( CASE Amount WHEN < 0 THEN Amount ELSE 0 END) AS Debits FROM TRANS GROUP BY Nominal"Can anyone please point out where I'm going wrong.Many thanksLuke |
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-04-25 : 11:54:04
|
| Your SQL syntax should be like this:"SELECT Nominal, SUM( CASE WHEN Amount < 0 THEN Amount ELSE 0 END) AS Debits FROM TRANS GROUP BY Nominal"(Instead of "CASE Amount ...", should be "CASE WHEN Amount < 0 ..") |
 |
|
|
lukeodonoghue
Starting Member
2 Posts |
Posted - 2005-04-25 : 12:24:38
|
| Many thanks,I'll blame a misprint in another source I was using.Now working like a dream! |
|
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-04-25 : 15:00:04
|
| You could have used the "CASE Amount WHEN ..." syntax if you were evaluating specific values, like "CASE Amount WHEN 100 THEN Amount ...", but you cannot use that syntax when using other types of comparative expressions, like greater than or less than. |
|
|
|
|
|
|
Problem with select case and aggregate functions