L.L L.L - 1 month ago 10
SQL Question

In Oracle, how to select specific row while aggregating all rows

I have a requirement that I need to both aggregate all rows by id, and find 1 specific row among the rows of the same id. It's like 2 SQL queries, but I want to make it in 1 SQL query. I'm using Oracle database.
for example,table t1 whose data looks like:

id | name | num
----- -------- -------
1 | 'a' | 1
2 | 'b' | 3
2 | 'c' | 6
2 | 'd' | 6


I want to aggregate the data by the id, find the 'name' with the highest 'count', and sum all count of the id to 'total_count'.
There are 2 rows with same num, pick up the first one.

id | highest_num | name_of_highest_num | total_num | avg_num
----- ------------- --------------------- ------------ -------------------
1 | 1 | 'a' | 1 | 1
2 | 6 | 'c' | 15 | 5


Can I get this result by 1 Oracle SQL query?
Thanks in advance for any replies.

MT0 MT0
Answer

Oracle Setup:

CREATE TABLE table_name ( id, name, num ) AS
SELECT 1, 'a', 1 FROM DUAL UNION ALL
SELECT 2, 'b', 3 FROM DUAL UNION ALL
SELECT 2, 'c', 6 FROM DUAL UNION ALL
SELECT 2, 'd', 6 FROM DUAL;

Query:

SELECT id,
       MAX( num ) AS highest_num,
       MAX( name ) KEEP ( DENSE_RANK LAST ORDER BY num ) AS name_of_highest_num,
       SUM( num ) AS total_num,
       AVG( num ) AS avg_num
FROM   table_name
GROUP BY id

Output:

ID HIGHEST_NUM NAME_OF_HIGHEST_NUM TOTAL_NUM AVG_NUM
-- ----------- ------------------- --------- -------
 1           1 a                           1       1 
 2           6 d                          15       5 
Comments