CODEWITHSUNDEEP
sql-servert-sql
user917670
user917670

Reputation: 871

SQL query for data integrity

I have a table with the following structure

key1 varchar(255)
key2 varchar(255)
value decimal(6,2)

now i want to retrieve all tuples grouped on one of the keys where the value doesnt add up to a constant value say 1000.00.

Any idea how I can get this done using SQL?

SAmple data

key1 key2  value
1    2     480
1    3     520
2    3     200
2    4     300
2    5     400

In this case my query should return the last 3 rows.

Upvotes: 1

Views: 301

Answers (1)

Martin Smith
Martin Smith

Reputation: 453338

;WITH t
     AS (SELECT *,
                SUM(value) OVER (PARTITION BY key1) AS s
         FROM   yourtable)
SELECT key1,
       key2,
       value
FROM   t
WHERE  s <> 1000

Upvotes: 3

Related Questions