Group by in Oracle SQL

Question

I have many rows with the fields name, unit, amount. The rows could be

name, unit, amount
========
Some name, A, 100
Some name, B, 300
Another name, A, 400

I want to select all the rows but grouped by name.

The unit can be either A or B. I want an output as:

name, A_amount, B_amount
============
Some name, 100, 300
Another name, 400, 0

So depending on the unit the amount should either be in A_amount or B_amount.

what you want is a pivot table, there are a lot example on SO. The Solution is also depending on your oracle version. Have a look here: http://stackoverflow.com/questions/4841718/oracle-sql-pivot-query — deterministicFail, Aug 20 '14 at 06:41

score 2 · Answer 1 · answered Aug 20 '14 at 06:48

2

You need a pivot operation on your base table to achieve this result.

SELECT *
FROM (SELECT name, unit, amount FROM   table1)
PIVOT (amount AS amount FOR (unit) IN ('A' AS a, 'B' AS b))
ORDER BY name;

This query should work for you. Refer this tutorial to learn more about pivot operations.

answered Aug 20 '14 at 06:48

CodeNewbie

1

this is only supported from Oracle 11 upwards – deterministicFail Aug 20 '14 at 06:51
1

I get the error message ORA-08634. I'm pretty sure that I use Oracle 11. – Jamgreen Aug 20 '14 at 07:26
@Jamgreen: people usually don't just know the message for any given error code. You need to include the actual error *message* as well. – Aug 20 '14 at 14:35

score 0 · Answer 2 · answered Aug 20 '14 at 14:34

Can you try the following query

/For query 1/

      Select * from #tblsample A
      inner join #tblsample2 B
       On A.ProductId=B.ProductId

/FOR QUERY 2/

        select * from #tblsample
         except
        select * from #tblsample2

/alternate way for query 2/

          select * from #tblsample
          where productid NOT IN(
          select * from #tblsample2)

2 Answers2