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....)
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....)
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'.
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
ASKER
Layout was perfect but for some strange reason it returned zero count in all three columns...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the query below only counts the orders.
You need to be more specific on the "value" you want to sum.
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
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
COUNT(ProdAOnly.OrderId) AS ProductAOnlyCount
,COUNT(ProdAB.OrderId) AS ProductABCount
,COUNT(ProdAOther.OrderId)
Open in new window