I probably should have written this one first
Most of these are exactly the same as in SQL Server. There are a whole bunch of interesting analytic functions, most of which should look pretty familiar to anyone who has spent time querying SQL Server. Most, if not all, can be extended to be window functions, if you need per-group analysis of any kind.
Counting!
All of this works the same as in SQL Server.
|
1 2 |
SELECT COUNT(*), COUNT(COMMISSION_PCT), COUNT(DISTINCT COMMISSION_PCT) FROM HR.EMPLOYEES; |
Oracle does have something kind of cool if you only need an approximate count of distinct values. Don’t ask me why there isn’t a similar function to get an approximate count of all values. I wasn’t invited to that meeting. This is good for really large data sets where you just need a rough of idea of the values you’re working with.
|
1 2 |
SELECT APPROX_COUNT_DISTINCT(COMMISSION_PCT) FROM HR.EMPLOYEES; |
Sums and Averages
Fun fact: under the covers, AVG is just a SUM and a COUNT anyway.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
--SUM and COUNT SELECT JOB_ID, SUM(SALARY) AS "DEPT_TOTAL", COUNT(JOB_ID) AS "POSITIONS", (SUM(SALARY) / COUNT(JOB_ID)) * .100 AS "AVG_SALARY" FROM HR.EMPLOYEES GROUP BY JOB_ID ORDER BY DEPT_TOTAL DESC; --AVG is a little easier SELECT JOB_ID, SUM(SALARY) AS "DEPT_TOTAL", COUNT(JOB_ID) AS "POSITIONS", AVG(SALARY) * .100 AS "AVG_SALARY" FROM HR.EMPLOYEES GROUP BY JOB_ID ORDER BY DEPT_TOTAL DESC; |
The Max for the Minimum
You also have your MIN and MAX functions, along with the HAVING clause, to filter aggregates.
|
1 2 3 4 5 |
SELECT JOB_ID, MIN(SALARY), MAX(SALARY) FROM HR.EMPLOYEES GROUP BY JOB_ID HAVING MIN(SALARY) > 10000 ORDER BY JOB_ID; |
Something not boring
The LISTAGG function is something I’d absolutely love to have something like in SQL Server. It takes column values and gives you a list per row, delimited by the character of your choice. It’s pretty sweet, and the syntax is a lot easier to bang out than all the XML mumbo jumbo in SQL Server.
|
1 2 3 4 |
SELECT JOB_ID, LISTAGG(EMPLOYEE_ID, ', ') WITHIN GROUP (ORDER BY EMPLOYEE_ID) AS "EMPLOYEE_IDS" FROM HR.EMPLOYEES GROUP BY JOB_ID ORDER BY JOB_ID; |
For reference, to do something similar in SQL Server, you need to do this:
|
1 2 3 4 5 6 7 8 |
SELECT [e].[JobTitle] , STUFF(( SELECT ', ' + CAST([e1].[BusinessEntityID] AS VARCHAR) FROM [HumanResources].[Employee] AS [e1] WHERE [e1].[JobTitle] = [e].[JobTitle] FOR XML PATH('') ), 1, 2, '') AS [EMPLOYEE_IDS] FROM [HumanResources].[Employee] AS [e] GROUP BY [e].[JobTitle]; |
Good luck remembering that!
SQL is a portable skill
Once you have the basics nailed down, and good fundamentals, working with other platforms becomes less painful. In some cases, going back is the hardest part! My knowledge of Oracle is still very entry level, but it gets easier and easier to navigate things as I go along. I figure if I keep this up, someday I’ll be blogging from my very own space station.
Thanks for reading!
11 Comments. Leave new
We really need LISTAGG. All the other kids on the block have it (Oracle, Postgres STRING_AGG, MySQL GROUP_CONCAT).
MySQL doesn’t even have CTEs and they have this. Postgres just got parallelism and they’ve had this. Oracle… uh… Whatever. Read this in call caps for effect.
The amount of time and effort that is expended on forums answering questions about this is astronomical. And just when you think it’s all figured out, you get “& #x0D;” in your results.
MySQL is more interested in helping you be terrible than helping you be awesome.
Heck, MySQL world is pretty good these days: few solid working forks, a lot of tested cluster/replication solutions that actually work. InnoDB/XtraDB engine is nicely scaling with hardware while people are getting other engines like Rocks or Toku to work with it successfully.
Programming-wise? Can’t say anything specific, it’s just I’m more fond of database languages derrived from ADA. And T-SQL is only one that stands out in that flock.
Hi
Just wondering why you’re multiplying the AVG_SALARY fields by .100? Wouldn’t that give you 1/10th of the right figure? Or is it a typo for multiplying by 1.00 to get an average to two decimal places?
Not-an-oracle-guy
I wrote this post so long ago that I forget.
I wondered about the same thing…..seems to be an error.
APPROX_COUNT_DISTINCT is a fascinating function (to me at least).
It uses the HyperLogLog function – cool name for a function!
https://justdaveinfo.wordpress.com/2016/06/08/oracle-approx_count_distinct-function/
Thank you for Oracle Week! I am really excited about this. I’ve been a SQL Server DBA for over 10 years now and I’m having to learn Oracle now. This post is just what I need!
Glad you’ve been enjoying it. I really enjoyed writing it. Good luck with the Oracle-ing.