CODEWITHSUNDEEP
sqlsql-serverselect
Wramana
Wramana

Reputation: 193

How to return only one result from sql table?

I have the following database table:

LNr OrderNr Ident Amount
1   128     3     123.00
2   128     14    200.00
3   1290    3     300.00
4   13400   3     637.00

I want to calculate the sum of the Amount-fields where Ident equals 3, but not 14 at the same time. So I want the database server to return only row number 3 and 4(where OrderNr = 1290 and 13400).

I tried:

SELECT SUM(Amount) FROM table WHERE Ident = '3'

But that does not work of course, because this also returns row 1 (which I do not want because Ident equals 14). I tried some other queries, but to no avail.

Upvotes: 2

Views: 690

Answers (3)

BICube
BICube

Reputation: 4681

Or you may do that using not in 

SELECT SUM(Amount)
FROM mytable
WHERE Ident=3 AND OrderNr NOT IN (SELECT OrderNr
                                  FROM mytable
                                  WHERE Ident=14)

Upvotes: 1

Greg
Greg

Reputation: 4035

The only way to do that is to further qualify your WHERE clause, by either excluding row 1, or including just row 3 and 4. You need additional filtering. There must other tables joined to that table that should allow you to filter gracefully based on whatever business rules you have defined.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311163

You can add another not exists condition:

SELECT SUM(Amount) 
FROM   mytable x
WHERE  Ident = 3 AND
       NOT EXISTS (SELECT *
                   FROM   mytable y
                   WHERE  x.OrderNr = y.OrderNr AND y.Ident = 14)

Upvotes: 6

Related Questions