CODEWITHSUNDEEP
sqlt-sqljoingroup-byunion
noob
noob

Reputation: 3811

SQL: Query UNION Error while joining 2 tables

I have 2 datasets, Products and OrderDetails (Northwind dataset)

Products dataset

ProductID   ProductName SupplierID  CategoryID  QuantityPerUnit     UnitPrice   UnitsInStock 
1               Chai        1              1     10 boxes x 20 bags   18.00             39         
2                Chang      1              1     24 - 12 oz bottles   19.00             17        
3              Aniseed      1              2     12 - 550 ml          10.00             13         
4            Seasoning      2              2     48 - 6 oz jars       22.00             53  
5             Gumbo Mix     2              2     36 boxes             21.35              0

Order Details dataset

ProductID   UnitPrice   OrderID Quantity    Discount
1             18.00     10248   10            0
42              9.80    10248   10            0
72             34.80    10248   5             0
1              18.00    10249   10            0
51             42.40    10249   40            0   and so on
1              18.00    10270   12            0

Query

SELECT ProductName, Count([Order Details].OrderID)
FROM ([Order Details] UNION Products ON [Order Details].OrderID = Products.OrderID)
WHERE [Order Details].Quantity = 10
GROUP BY ProductName

Expected Output

Product Name (from Products dataset)    How many OrderIDs had this product where quantity = 10
Chai                                     2

Here since 2 order IDs had Chai, 10248 and 10249, where quantity = 10, we only considered these cases. But I am getting error in query' '

Incorrect syntax near the keyword 'UNION'.

Upvotes: 0

Views: 58

Answers (3)

Darshini A
Darshini A

Reputation: 11

Select p.ProductName , count(o.ProductID) from dbo.Products p
join dbo.Orders o
on p.ProductID = o.ProductID where o.Quantity = '10'
group by o.ProductID, p.ProductName

Hope this helps.

Upvotes: 1

zealous
zealous

Reputation: 7503

Try the following.

select
    productName,
    count(*) as total_orders
from products p
join order o
on p.productId = o.productId
where Quantity = 10
group by
    productName

Upvotes: 1

GMB
GMB

Reputation: 222572

You seem to want a JOIN rather than UNION. However, for this dataset, I would go for a correlated subquery:

select
    p.*,
    (
        select count(*) 
        from orderDetails od 
        where od.productID = p.productID and od.quantity = 10
    ) no_orders
from products p

The upside of this approach are that:

  • it avoids outer aggregation

  • you get a results also for products that do not have a matching orders (they would get a count of 0)

  • the subquery is executed only once per product anyway, so there is no penalty compared to the join approach; I would expect that this would perform equally well, or better, assuming an index on orderDetails(productID, quantity)

Upvotes: 1

Related Questions