CODEWITHSUNDEEP
sqlsql-serversql-server-2008
M.G
M.G

Reputation: 63

SQL SUM with Conditions

+--------+--------+-------+-------+
| PartId | ItemId | Price | Staus |
+--------+--------+-------+-------+
|  94669 |   3678 |    88 |     1 |
|  94669 |   3679 |    22 |     1 |
|  94669 |   3680 |    30 |     1 |
|  94669 |   3681 |    50 |     4 |
|  94670 |   3678 |    88 |     1 |
|  94670 |   3679 |    22 |     1 |
|  94670 |   3680 |    30 |     1 |
|  94670 |   3681 |    50 |     1 |
+--------+--------+-------+-------+

The expected output is (without status 4)

+--------+------------+
| PartId | TotalPrice |
+--------+------------+
|  94669 |        140 |
|  94670 |        190 |
+--------+------------+

How can I achieve this in SQL?

Upvotes: 1

Views: 66

Answers (2)

user6373040
user6373040

Reputation:

You can try:

SELECT "Part ID", SUM(Price)
FROM (
    SELECT "Part ID", Price
    FROM Table
    WHERE Status <> 4)
GROUP BY "Part ID";

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

Pretty basic aggregation query with a where and group by.

select partid, sum(price) as totalprice
from t
where [status] <> 4
group by partid

If you have to omit status other than 1, it would be correct to use status = 1 instead:

select partid, sum(price) as totalprice
from t
where [status] = 1
group by partid

Upvotes: 4

Related Questions