SQL query not showing the right result when using LIKE

I am using Microsoft SQL Server Management Studio 2018 for my queries and I am trying to get the total quantity of ‘Cavity’ part types for each order and I have notice some anomaly for 1 order. I am 99% sure that it has been properly added to the SQL database as each order is first created in Access and the .mdb file for this order had 40 ‘Cavity’ parts only. However, when I run an SQL query, I get 248 a total of 248 parts.

So far, I have tried playing around with code, different joins, removing / adding different columns to the SELECT and GROUP BY clause and only filtering out parts using NOT LIKE method helps.

The following code (for 2 orders to show the differences) returns me 2 columns and 2 rows (as expected):

SELECT OrderNo, SUM(Quantity) FROM Orders

INNER JOIN Heading ON Orders.OrderID = Heading.OrderID

WHERE ComponentType LIKE 'Cavity' AND OrderNo LIKE '73966' OR OrderNo LIKE '73967'

GROUP BY OrderNo, Quantity

However, I get that order 73966 has 40 cavity parts (correct) and that order 73967 has 248 (incorrect).

When I tried changing the query and adding ComponentType (to SELECT and GROUP BY) this is what I got:

Order | Quantity | ComponentType
73966    40       Cavity
73967    16       
73967    60       Bead type
73967    40       Cavity
73967    8        Cill
73967    42       Frame
73967    48       Sash
etc...      

Adding the following helps to filter out the unwanted components but I don’t think it should be necessary to get the right results, as adding anything else in the future will force me to update the query.

 WHERE (...) AND ComponentType NOT LIKE '%Bead type%'

I think it’s worth mentioning that both orders have same component types (Bead type, Cavity, Cill) and it’s just the order number 73967 not showing properly. Any ideas why I need to filter all other part types for this order manually rather than SQL doing it for me?

Answer

You are using OR clause in wrong way .you need () for check both OR condition

SELECT OrderNo, SUM(Quantity) FROM Orders
INNER JOIN Heading ON Orders.OrderID = Heading.OrderID
WHERE ComponentType LIKE 'Cavity' 
AND  ( OrderNo LIKE '73966' OR OrderNo LIKE '73967')
GROUP BY OrderNo

and you should not use column in aggregation function (quantity ) in group by (in this way the sum fails

anyway if don’t use wildchar then you should use = and not like

SELECT OrderNo, SUM(Quantity) FROM Orders
INNER JOIN Heading ON Orders.OrderID = Heading.OrderID
WHERE ComponentType =  'Cavity' 
AND  ( OrderNo = '73966' OR OrderNo = '73967')
GROUP BY OrderNo

or as suggested you can use a IN clause for instead fo several OR

SELECT OrderNo, SUM(Quantity) FROM Orders
INNER JOIN Heading ON Orders.OrderID = Heading.OrderID
WHERE ComponentType =  'Cavity' 
AND   OrderNo IN( '73966','73967')
GROUP BY OrderNo