Link to home
Start Free TrialLog in
Avatar of M_Boy76
M_Boy76

asked on

SQL Logic Query

Hello

I have two tables an orderline table and an item tables, the orderline table has an orderID and a stockID as the unquie keys, while the item table has the stockID as the primary key with a field knows as productclass.

I want execute a query that will count the number of orders with their value, in which there was a product  A ordered alone, and another column that will count the number of orders in which product  A  exist with product  B  in particular, and another column that will count the number of orders with product  A and any other product (Product C, D, E, F....)
Avatar of reb73
reb73
Flag of Ireland image

How about this ?
SELECT	 COUNT(ProdAOnly.OrderId) AS ProductAOnlyCount
	,COUNT(ProdAB.OrderId) AS ProductABCount
	,COUNT(ProdAOther.OrderId) AS ProductAOtherCount
FROM
	(SELECT OrderId, COUNT(DISTINCT StockId) FROM T_OrderLine WHERE TL.StockId LIKE 'Product A' 
	 GROUP BY OrderId HAVING COUNT(DISTINCT StockId) = 1
	) ProdAOnly
RIGHT JOIN
	(SELECT OrderId, COUNT(DISTINCT StockId) FROM T_OrderLine WHERE (TL.StockId LIKE 'Product A' OR TL.StockId LIKE 'Product B')
	 GROUP BY OrderId HAVING COUNT(DISTINCT StockId) >= 2
	) ProdAB
	ON ProdAOnly.OrderId = ProdAB.OrderId
RIGHT JOIN
	(SELECT OrderId, COUNT(DISTINCT StockId) FROM T_OrderLine WHERE (TL.StockId LIKE 'Product A' AND TL.StockId NOT LIKE 'Product B')
	 GROUP BY OrderId HAVING COUNT(DISTINCT StockId) >= 2
	) ProdAOther
	ON ProdAOther.OrderId = ProdAB.OrderId

Open in new window

Avatar of M_Boy76
M_Boy76

ASKER

I got the following error:

Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'ProdAOnly'.
Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'ProdAB'.
Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'ProdAOther'.
Sorry.. Try -
SELECT   COUNT(ProdAOnly.OrderId) AS ProductAOnlyCount
        ,COUNT(ProdAB.OrderId) AS ProductABCount
        ,COUNT(ProdAOther.OrderId) AS ProductAOtherCount
FROM
        (SELECT OrderId, COUNT(DISTINCT StockId) AS TempCount FROM T_OrderLine WHERE TL.StockId LIKE 'Product A' 
         GROUP BY OrderId HAVING COUNT(DISTINCT StockId) = 1
        ) ProdAOnly
RIGHT JOIN
        (SELECT OrderId, COUNT(DISTINCT StockId) AS TempCount FROM T_OrderLine WHERE (TL.StockId LIKE 'Product A' OR TL.StockId LIKE 'Product B')
         GROUP BY OrderId HAVING COUNT(DISTINCT StockId) >= 2
        ) ProdAB
        ON ProdAOnly.OrderId = ProdAB.OrderId
RIGHT JOIN
        (SELECT OrderId, COUNT(DISTINCT StockId) AS TempCount FROM T_OrderLine WHERE (TL.StockId LIKE 'Product A' AND TL.StockId NOT LIKE 'Product B')
         GROUP BY OrderId HAVING COUNT(DISTINCT StockId) >= 2
        ) ProdAOther
        ON ProdAOther.OrderId = ProdAB.OrderId

Open in new window

Avatar of M_Boy76

ASKER

Layout was perfect but for some strange reason it returned zero count in all three columns...
ASKER CERTIFIED SOLUTION
Avatar of reb73
reb73
Flag of Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
the query below only counts the orders.
You need to be more specific on the "value" you want to sum.
WITH Orders_View as (
  SELECT orderID,
         sum(case stockID 
                 when 'product A ID' then 1
                 else 0
            end) PA,
         sum(case stockID 
                 when 'product B ID' then 1
                 else 0
            end) PB,
         sum(case stockID 
                 when 'product A ID' then 0
                 when 'product B ID' then 0
                 else 1
            end) otherP
    from orderline
  group by orderID
)
SELECT sum(case when PA=1 and PB=0 and otherP=0 then 1 else 0 end) PA_only,
       sum(case when PA=1 and PB=1 /*and otherP=0*/ then 1 else 0 end) PA_PB,
       sum(case when PA=1 and PB=0 and otherP>=1 then 1 else 0 end) PA_other
  FROM Orders_View

Open in new window

Avatar of M_Boy76

ASKER

@ reb73: what is i want to sum the unit price for each of

COUNT(ProdAOnly.OrderId) AS ProductAOnlyCount
        ,COUNT(ProdAB.OrderId) AS ProductABCount
        ,COUNT(ProdAOther.OrderId) AS ProductAOtherCount