This discussion is archived
1 Reply Latest reply: Jul 7, 2011 4:13 PM by mikereiche RSS

Group by clause issue

867085 Newbie
Currently Being Moderated
Hi all,

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


prom_id id_ru_prd     
1 123
2 345


id_ru_prdv item
123 A
345 B

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?
  • 1. Re: Group by clause issue
    mikereiche Pro
    Currently Being Moderated
    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


  • Correct Answers - 10 points
  • Helpful Answers - 5 points