CODEWITHSUNDEEP
sqlsql-serversum
Oxymoron
Oxymoron

Reputation: 1452

alternative to sum in sql where clause

I have table1 which represents an order summary. In table1 has a one to many relationship with table2, which represents the different items that are part of the order. table1 has a field for quantity, which should represent the total number of individual items that are part of the order. In table2 the quantity field that represents the number of individual items for that item type.

I want to see what is not conforming to this rule (I have reason to believe the rule was broken)

SELECT table1.id, table1.quantity, table2.orderid, table2.itemqty
FROM table1
INNER JOIN table2 on tabel1.id = table2.orderid
WHERE table1.quantity != SUM(table2.itemqty)

Upvotes: 1

Views: 3753

Answers (2)

Andomar
Andomar

Reputation: 238058

You can use a having clause to apply a condition to a group:

select  t1.id
,       t1.customer
,       t1.quantity 
,       sum(t2.itemqty)
from    table1 t1
join    table2 t2
on      t1.id = t2.orderid
group by
        t1.id
,       t1.customer
,       t1.quantity 
having  t1.quantity <> sum(t2.itemqty)

Upvotes: 3

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

SELECT     table1.id, 
           table1.quantity, 
           table2.orderid, 
           table2.itemqty
FROM       table1
INNER JOIN ( 
               SELECT orderid,
                      SUM(itemqty) as itemqty 
               FROM   table2 
               GROUP BY orderid
           ) table2 
           on tabel1.id = table2.orderid
WHERE      table1.quantity <> table2.itemqty

Upvotes: 1

Related Questions