0

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?

Improve this question
4
  • 1
    Your description and sample data do not match. Commented May 27, 2016 at 22:45
  • 1
    Why sum(num3)? Your final dataset just has one row from the original data, without any aggregation, which doesn't seem to match what you've said. Was "one column per user" supposed to be "one row..."? If you want one row, how will you decide which one? Commented May 27, 2016 at 22:56
  • GordonLinoff, it was just a random example. @Alex Poole, you are correct, how is the decision made for which one to keep? I am trying to reproduce a data set that someone else did, but with a different table with different fields. So with this table I am getting these unwanted results. But I put my query into SSRS that is able to do the grouping, and it just chose the first row, which is not correct. So not sure what to do. Commented May 27, 2016 at 23:06
  • 2
    A random example that is not right, and is confusing to your question. Matching your actual request, NUM3 should return 63, 138 and 355. But given that fact, I think I got your answer. Commented May 27, 2016 at 23:12

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.