Group By Clause - Oracle SQL

Question

I have to display about 5 columns from 2 database tables using the Group By clause as follows:

SELECT T1.COLUMN_A, T1.COLUMN_B, T1.COLUMN_C, SUM(T2.COLUMN_D)
FROM TABLE1 T1, TABLE2 T2
WHERE T1.COLUMN_A = T2.COLUMN_A
GROUP BY T1.COLUMN_A

Now COLUMN_B has the same value across all the rows having the same COLUMN_A and COLUMN_B is a amount field. COLUMN_C is a date field and would be same across the same COLUMN_A values.

Ex. Here is dummy data TABLE T1

COLUMN_A           COLUMN_B          COLUMN_C
1                    $25               09/15/2911 12:00:00 AM
1                    $25               09/15/2011 12:00:00 AM
2                    $20               12/12/2011 12:00:00 AM
...

TABLE T2:

COLUMN_A       COLUMN_D
1                $100
1                $10
2                $200
2                $200
.....

Running the query does not work with following error: ORA-00979: not a GROUP BY expression

Removing COLUMN_B and COLUMN_C would work. However I need these columns as well.

Can anyone please suggest the required changed?

Answer 1

If the values of COLUMN_B and COLUMN_C are the same across same values of COLUMN_A, then you can simply add them to theGROUP BY clause:

SELECT T1.COLUMN_A, T1.COLUMN_B, T1.COLUMN_C, SUM(T2.COLUMN_D)
FROM TABLE1 T1, TABLE2 T2
WHERE T1.COLUMN_A = T2.COLUMN_A
GROUP BY T1.COLUMN_A, T1.COLUMN_B, T1.COLUMN_C

You've specified columns COLUMN_B and COLUMN_C in your SELECT list, so Oracle will need to provide a value for them when GROUPing BY COLUMN_A. However. Oracle doesn't know that these columns are constant across same values of COLUMN_A, and you get the error because in general it has no way of returning a value for these columns.

Adding COLUMN_B and COLUMN_C to the GROUP BY clause shouldn't affect the results of the query and should allow you to use them in your SELECT list.

Answer 2

This should work

SELECT T1.COLUMN_A, T1.COLUMN_B, T1.COLUMN_C, SumColumnD
FROM TABLE1 T1 
     INNER JOIN 
     (SELECT COLUMN_A, SUM(COLUMN_D) AS SumColumnD
      FROM TABLE2 T2
      GROUP BY COLUMN_A) t ON T1.COLUMN_A = t.COLUMN_A