• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

Query Help

Hi All - need help writing a query for MSSQL - cannot seem to get it right

I have a table of orders o

order_id int pk
sub_total  money

I have another table of lineitems l

lineitem_id int pk
order_id fk to o.orders_id
linetotal money

An there may be many lineitems in an order

I am trying to write a query that finds any order that has a sub total that is <> to the sum of its lineitems (linetotals).
1 Solution
Simone BSenior E-Commerce AnalystCommented:
Try this:

SELECT o.order_id, o.sub_total, SUM(l.linetotal) AS linetotal
FROM orders o
INNER JOIN lineitems l ON o.order_id = l.order_id
GROUP BY o.order_id, o.sub_total
HAVING o.sub_total <> SUM(l.linetotal)

Open in new window

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
You can use a <> in a JOIN clause to find non-matches.  

Copy-paste the below T-SQL into your SSMS, execute to verify it works for your question, then modify to meet your needs.
CREATE TABLE orders (order_id int, sub_total money)
CREATE TABLE lineitems (lineitemid int, order_id int, sub_total money)

INSERT INTO orders (order_id, sub_total)
VALUES (1, 100), (2, 200), (3, 300) 

INSERT INTO lineitems (lineitemid, order_id, sub_total) 
	-- 1 is good 
	(1, 1, 30), 
	(2, 1, 70), 
	-- 2 is bad
	(3, 2, 42), 
	(4, 2, 99), 
	(5, 2, 86), 
	-- 3 is good
	(6, 3, 100), 
	(7, 3, 75), 
	(8, 3, 125) 

SELECT o.order_id, o.sub_total, l.sub_total
FROM orders o
   JOIN (SELECT order_id, SUM(sub_total) as sub_total FROM lineitems GROUP BY order_id) l ON o.order_id = l.order_id AND o.sub_total <> l.sub_total

Open in new window

ccleebeltAuthor Commented:
Ah.  I was so close.  Thanks!
Anthony PerkinsCommented:
I would do it this way:
SELECT  o.order_id,
FROM    orders o
        INNER JOIN (
                    SELECT  order_id,
                            SUM(linetotal) Sumlinetotal
                    FROM    lineitems
                    GROUP BY order_id
                   ) l ON o.order_id = l.order_id
WHERE   o.sub_total <> l.Sumlinetotal

Open in new window

Anthony PerkinsCommented:
The reason I think my solution is better is because I see no reason to do a GROUP BY on the orders table.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now