Finding an Item based on certain criteria in SQL-CodePudding

I have the following table structure:

Transaction table:

code    type        action  level   id  qty   OrderID
====================================================
10005   PRODUCT     RECIPE  0       0   1     O-123  
821     VALUE_MEAL  RECIPE  0       1   1     O-123
9       PRODUCT     RECIPE  1       1   1     O-123
7093    PRODUCT     RECIPE  1       1   1     O-123
10005   PRODUCT     CHOICE  1       1   1     O-123
721     VALUE_MEAL  RECIPE  0       2   1     O-123
9       PRODUCT     RECIPE  1       2   1     O-123
7093    PRODUCT     RECIPE  1       2   1     O-123
10005   PRODUCT     CHOICE  1       2   1     O-123

My Master table:

code      FullName   MenuCategory 
---------------------------------
821         ML2       Group2  
721         ML1       Group2   
9           fires     Group2   
10005       Orange    Group3

Expected result set:

code    QTY with Meal   Sold Alone
------------------------------------
10005        2              1

In the expected result, we need to differentiate if an item code "10005" was sold with a VALUE_MEAL that has different level and id or it was sold alone as a PRODUCT within the same order?

Example: Code=821, contains three main sub-level which are (9, 7093, 10005) and code=721, contains three main sub-level which are (9, 7093, 10005) so here we know 2 QTY were sold for 10005 as it's part of both VALUE_MEALS and one QTY for code = 10005 as part of standalone product

CodePudding user response：

It looks like you just want a grouped self-join, with conditional aggregation

SELECT
  t.code,
  [QTY with Meal] = COUNT(t2.IsMeal),  -- only counts non nulls
  [Sold Alone] = COUNT(*) - COUNT(t2.IsMeal)
FROM [Transaction] t
CROSS APPLY (
    SELECT IsMeal =  CASE WHEN COUNT(*) > 0 THEN 1 ELSE NULL END
    FROM [Transaction] t2
    WHERE t2.id = t.id
      AND t2.type = 'VALUE_MEAL'
) t2
WHERE t.code = 10005
GROUP BY
  t.code;

You could also switch the COUNT(*) in the subquery for an EXISTS

CodePudding user response：

For each (OrderID, code) unique pair, the query uses the 'level' column to determine whether or not items have been combined in a meal

select OrderID, code, 
       sum(case when [level]>0 then 1 else 0 end) [QTY with Meal],
       sum(case when [level]=0 then 1 else 0 end) [Sold Alone]
from TransactionTable
group by OrderID, code
order by OrderID, code;