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?
I'm having a hard time understanding what you are trying to do.
Can you write what you want in SQL, and then we'll see if we can get the same result in XQuery?
I think what you want is the union of :
select trans, count(*) from transaction_table group by trans where prom is null
select trans, count(*) from transaction_table group by trans where prom is not null
Edited by: mikereiche on Jul 7, 2011 4:11 PM