1 Reply Latest reply: Jul 7, 2011 6:13 PM by Mikereiche-Oracle RSS

    Group by clause issue

      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
          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