SQL Series | Using Aggregate Functions - Part I

 Hello Again,

Can’t believe we are already in March! There is a lot happening globally at all levels – geo-politics, technology, you name it, it feels like everything is on steroid mode 🤔 But today I would like to take a step back and focus some of the basic functions in SQL (yeah, you read it right)

We’ll try to look at three ‘Aggregate functions’ from Oracle EBS schema perspective. As the name indicates - Aggregate functions take multiple input rows and return a single summary value per group (or per entire result set).

So, let's get start (the third one is my favorite)

1. COUNT - returns number of rows

Let's try and understand this with examples

count(*) - counts all rows and includes rows where some OR all columns can be NULL as well

select count(*) as rowcount from per_all_people_f;

 

count(column_name) - will return a count of only non-NULL values in that column. All rows where that column name is NULL will be ignored in this aggregate function

select count(DISTINCT(first_name)) from per_all_people_f;

count(DISTINCT(column_name)) - will count distinct non-NULL values in that column but it still ignores NULL 

select count(DISTINCT(first_name)) from per_all_people_f;

So, you can see that even though the function is the same (COUNT) but when used as per specific context it will fetch the result differently. 

=======================

2. SUM – returns the total of a numeric expression.

Usually used in payroll and balance related tables, but not restricted to them only. Can be used to get headcount or can be used with CASE statement for conditional totals

SELECT 
    SUM(CASE WHEN employment_category = 'FTE' THEN 1 ELSE 0 END) AS FullTimeExempt_count,

    SUM(CASE WHEN employment_category = 'FTP' THEN 1 ELSE 0 END) AS FullTimePerm_count,

    SUM(CASE WHEN employment_category = 'FTT' THEN 1 ELSE 0 END) AS FullTimeTemp_count
FROM   per_all_assignments_f
WHERE  primary_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN trunc(effective_start_date) AND trunc(effective_end_date);

And now (drumroll please 🥁🥁🥁)

=======================

3. LISTAGG – concatenates values into a single string

One my personal favorite. As I get lots of requests from business users to provide person and assignment data all in one single row. So ‘Listagg’ is my go-to function

SELECT

full_name,

(  select  LISTAGG (employment_category||'|'||assignment_number||'|'||primary_flag, '; ')  

            WITHIN GROUP (ORDER BY person_id asc)

    from per_assignments_x PAX

    where PAX.person_id = PAPF.person_id) assgniment_details      

FROM    per_all_people_f PAPF

WHERE trunc(sysdate) between trunc(effective_start_date) and trunc(effective_end_date)

and employee_number = &p_employee_number

ORDER BY full_name asc;

What we are doing here is - 

1. LISTAGG takes each row's data i.e. employment_category | assg_number | primary_flag 
2. Combines all rows CON|60363492-2|N; CON|60363492-3|N; CON|60363492|Y
3. || = string concatenation (just like '+' in Excel)
4. '; ' = separator between each assignment
5. WITHIN GROUP (ORDER BY person_id ASC) i.e. sort before combining

Now some generic usages of listagg function can be - 

1. Showing all positions a person ever held
2. Quickly checking if someone has multiple jobs
3. Validating if a person has the right (primary assignment, legal employer) or some other business relevant combination

Hope you would have found this little piece of info somewhat helpful. 

Take care