CODEWITHSUNDEEP
sqloracle-databasegroup-by
Zolt
Zolt

Reputation: 2811

How to group by one field in Oracle?

Say I have a dataset like this: 

MANAGER  |  USER  |  NUM1  |  NUM2  |  NUM3
---------|--------|--------|--------|-------
Bob      |  Jane  |  9     |  88    |  2
Bob      |  Jane  |  34    |  32    |  52
Bob      |  Jane  |  32    |  111   |  9
Bob      |  Rick  |  64    |  1     |  102
Bob      |  Rick  |  12    |  41    |  16
Bob      |  Rick  |  4     |  13    |  20
Bob      |  Mark  |  87    |  1     |  333
Bob      |  Mark  |  342   |  41    |  16
Bob      |  Mark  |  54    |  813   |  6

As you can see the number columns are all different, the manager is always the same, and the user is repeating.

I'd like to to group by user so that only one column per user shows up.

So the final dataset would look like this:

MANAGER  |  USER  |  NUM1  |  NUM2  |  NUM3
---------|--------|--------|--------|-------
Bob      |  Jane  |  9     |  88    |  2
Bob      |  Rick  |  64    |  1     |  102
Bob      |  Mark  |  87    |  1     |  333

I'd like to run something like this: 

SELECT MANAGER, USER, NUM1, NUM2, SUM(NUM3) AS NUM3
FROM MYTABLE
GROUP BY USER

This code will not work since you need to group by all aggregate functions. I don't want to use any aggregate functions on the other columns since they will change the values for NUM1 and NUM2. Is there any way around this? Anyone know what I can do to make this happen?

Upvotes: 0

Views: 2867

Answers (1)

GolezTrol
GolezTrol

Reputation: 116180

A possible solution if you do need to get the sum over column NUM3. Use the analytical version (window function) of SUM. That, alongside ROW_NUMBER. This allows you to define what the first row is per group and still select the total sum for the group:

SELECT MANAGER, USER, NUM1, NUM2, NUM3
FROM
    (SELECT 
      MANAGER, USER, NUM1, NUM2, 
      ROW_NUMBER() over (partition by MANAGER, USER /* order by ....*/) AS USERROWNUMBER,
      SUM(NUM3) over (partition by MANAGER, USER) AS NUM3
    FROM MYTABLE)
WHERE
  USERROWNUMBER = 1

It would by nice to specify an order by clause in the ROW_NUMBER window function, otherwise Oracle may pick any order and might even return the rows in a different order on a different occasion. Sorting by an ID, or by, for instance, NUM1, would be helpful to get more consistent results.

If, after all you don't need to sum NUM3, you can just select it like NUM1 and NUM2, and leave the whole SUM expression out of it.

Upvotes: 2

Related Questions