Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Stephen.

Asked: October 18, 2016 - 6:59 pm UTC

Last updated: October 19, 2016 - 7:54 am UTC

Version: 4.1

Viewed 1000+ times

You Asked

Hi Chris or Connor,

Saw you guys at OOW so I thought I toss a basic SQL query to you. I’ll use the HR.EMPLOYEES table to represent my problem so forgive me if it's a bit contrived.

I’ll like to construct a query to sum the salaries grouped by department. So far straight-forward. However I only want to count salaries from people with unique first names. If a name appears more than once, say James or Julia, I want to only include the salary of the one who was originally hired first.

Most appreciative,
Stephen

and Connor said...

A little analytic can isolate the salaries we want

SQL> select first_name,
  2         department_id,
  3         case when row_number() over ( partition by first_name order by hire_date ) = 1 then salary end first_sal_only
  4  from   hr.employees;

FIRST_NAME           DEPARTMENT_ID FIRST_SAL_ONLY
-------------------- ------------- --------------
Adam                            50           8200
Alana                           50           3100
Alberto                         80          12000
Alexander                       30           3100
Alexander                       60
Alexis                          50           4100
Allan                           80           9000
Alyssa                          80           8800
Amit                            80           6200
Anthony                         50           3000
Britney                         50           3900
...
Vance                           50           2800
William                        110           8300
William                         80
Winston                         50           3200

107 rows selected.


From there, we just go back to our normal group by 

SQL> select department_id,
  2         sum(first_sal_only)
  3  from
  4  (
  5  select first_name,
  6         department_id,
  7         case when row_number() over ( partition by first_name order by hire_date ) = 1 then salary end first_sal_only
  8  from   hr.employees
  9  )
 10  group by department_id
 11  order by 1;

DEPARTMENT_ID SUM(FIRST_SAL_ONLY)
------------- -------------------
           10                4400
           20               19000
           30               22400
           40                6500
           50              128400
           60               15000
           70               10000
           80              281300
           90               58000
          100               43408
          110               20308
                             7000

12 rows selected.

SQL>

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

with 12c Pattern matching

Rajeshwaran, Jeyabal, October 19, 2016 - 2:51 am UTC 

demo@ORA12C> select department_id, sum(sal)
  2  from hr.employees
  3  match_recognize(
  4    partition by department_id
  5    order by first_name, hire_date
  6    measures
  7      sum(salary) sal
  8    one row per match
  9    pattern( b1+ )
 10    define
 11      b1 as prev(first_name) <> first_name or
 12            prev(first_name) is null )
 13  group by department_id
 14  order by department_id ;

DEPARTMENT_ID   SUM(SAL)
------------- ----------
           10       4400
           20      19000
           30      24900
           40       6500
           50     142300
           60      28800
           70      10000
           80     288700
           90      58000
          100      51608
          110      20308
                    7000

12 rows selected.

demo@ORA12C> select department_id, sum(sal)
  2  from (
  3  select department_id ,
  4    decode( row_number() over(partition by department_id, first_name
  5                order by hire_date) , 1, salary) sal
  6  from hr.employees
  7       )
  8  group by department_id
  9  order by department_id    ;

DEPARTMENT_ID   SUM(SAL)
------------- ----------
           10       4400
           20      19000
           30      24900
           40       6500
           50     142300
           60      28800
           70      10000
           80     288700
           90      58000
          100      51608
          110      20308
                    7000

12 rows selected.

demo@ORA12C>

partition over technique shenanigans

Stephen, October 19, 2016 - 3:10 am UTC

This is exactly what I was looking for! Is it kosher to extend this technique to another column like so:

select first_name,
department_id,
case when row_number() over ( partition by first_name order by hire_date asc) = 1 then salary end first_sal_only
case when row_number() over ( partition by first_name order by foo desc ) = 1 then bar end last_bar_only
from   hr.employees;

Chris Saxon
October 19, 2016 - 7:54 am UTC

Sure, if you're looking for the last entry by some other attribute, that works.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.