i am having some issue in grouping columns i have some specific criteria depending on that i need to group columns.
i have table as transaction_table in that i have
Transaction item prom_id
296 A null sale -- prom --Source item
296 B 1 sale -- prom --Target item
296 A null
298 A null
in above table for transaction 296 i have item A twice but out of them one item is sold in promotion with target item B and other is non promotional item A.
i have prom_id which tells me which target item is sold in promotion but to find out source item (in this example item A) i have column in other base table(promotion table) which tells me associated source item using prom_id
i need to calculate sum of quantity depending weather item was sold in promotion or not
result should be
item quantity promotion / non promotional
A 1 promotion
B 1 promotion
A 2 non promotional
in group by it doesn't allow sub query , can anyone help me on this pls?