In a LinkedIn debate about how to update from a select with summarized registers in SQL Server, Sergio Romero has shared an interesting tip about new summarize functions on Oracle 11g R2.
-- Using hr schema. Oracle 11.2 release -- SELECT job_title, SUM(t2.salary)SALARY_SUM, COUNT(t2.job_id)NUMBER_EMP, LISTAGG(last_name||' '||first_name||' '||salary, '; ') WITHIN GROUP (ORDER BY hire_date, last_name) "EMP_LIST"
FROM jobs t1, employees t2
WHERE t1.job_id=t2.job_id
GROUP BY t1.job_title
ORDER BY 2 desc
This is what he said:
Sum function works with numeric datatypes and non numeric datatypes that can be implicitly converted. For example, this solutions works on a numeric column and on a varchar2 column which contains numeric values (bad design).
But will not summarize char values. For char values, you must use string aggregation techniques:
- Oracle 11.2 implements the LISTAGG function (sweet)
- Oracle 10.1 and above MAY include the WM_CONCAT fuction, but is an unsupported and undocumented function.
- For Oracle 9i and above, you can use this great function created by asktom.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
Of course you can create your own user defined function
I have made a little script using SUM and LISTAGG functions:
-- Using hr schema. Oracle 11.2 release -- SELECT job_title, SUM(t2.salary)SALARY_SUM, COUNT(t2.job_id)NUMBER_EMP, LISTAGG(last_name||' '||first_name||' '||salary, '; ') WITHIN GROUP (ORDER BY hire_date, last_name) "EMP_LIST"
FROM jobs t1, employees t2
WHERE t1.job_id=t2.job_id
GROUP BY t1.job_title
ORDER BY 2 desc
Thanks for sharing this useful information, Sergio!