5 Replies Latest reply: Jun 1, 2008 1:29 PM by Rob van Wijk RSS

    ORA-00979: not a GROUP BY expression error.

    shivasha
      Hi I am calculating sum of amount by using below query
      It shows ORA-00979: not a GROUP BY expression error.
      SELECT
      a.ID,a.serial_num,b.id,
      a.num,a,currency,
      a.code,a.date,
      b.qty,b.amt,
      SUM(b.qty*b.amt) as totalamt
      FROM table1 a,table2 b
      group by id
      WHERE a.id=b.id

      Thanks
        • 1. Re: ORA-00979: not a GROUP BY expression error.
          Rob van Wijk
          Either do:
          select a.id
               , a.serial_num
               , b.id
               , a.num
               , a.currency
               , a.code
               , a.date -- really?
               , sum(b.qty * b.amt)
            from table1 a
               , table2 b
          where a.id = b.id
          group by a.id
               , a.serial_num
               , b.id
               , a.num
               , a.currency
               , a.code
               , a.date
          or:
          select a.id
               , a.serial_num
               , b.id
               , a.num
               , a.currency
               , a.code
               , a.date -- really?
               , b.qty
               , b.amt
               , sum(b.qty * b.amt) over (partition by a.id)
            from table1 a
               , table2 b
          where a.id = b.id
          A question like this gets a better answer when sample data and expected output are given.

          Regards,
          Rob.
          • 2. Re: ORA-00979: not a GROUP BY expression error.
            shivasha
            Thanks ROB..

            I need only totalamount as qty*amt
            For that i had changed the query as follows

            select a.id , a.serial_num , b.id , a.num , a.currency , a.code , really? , b.qty , b.amt , (b.qty * b.amt) as totalamt from table1 a , table2 b where a.id = b.id

            In Java, i didn't refer this alias name totalamt.
            Is there is any aggregate fuctions to do total?
            If i put SUM() means, amount is displayed as different value.
            How to do this?
            Thanks
            • 3. Re: ORA-00979: not a GROUP BY expression error.
              486393
              Show us the output you want because I don't get it.


              Do

              select a.id , a.serial_num , b.id , a.num , a.currency , a.code , really? , b.qty , b.amt , (b.qty * b.amt) as totalamt, sum(b.qty * b.amt) over (partition by id) as totalamt_id,
              sum(b.qty * b.amt) over () as totalamt_total
              from table1 a , table2 b
              where a.id = b.id

              What is the right total? Column totalamt,totalamt_id or totalamt_total?

              Message was edited by:
              user483390
              • 4. Re: ORA-00979: not a GROUP BY expression error.
                shivasha
                While using Partation by ID, the
                sum of amt is
                qty amount totalamt_total
                13 112392 54350856
                10 47376 54350856

                Amount calculation is not crrectly displayed
                Regards
                • 5. Re: ORA-00979: not a GROUP BY expression error.
                  Rob van Wijk
                  > Amount calculation is not crrectly displayed

                  But you still didn't tell us what you think is a correct output.

                  So I'll repeat myself: A question like this gets a better answer when sample data and expected output are given.

                  Regards,
                  Rob.