EnterpriseDB: Aggregation Oracle Tips by Burleson Consulting

Aggregation is combining multiple rows into a single result.  If we counted many rows and returned a single row of the count, that would be an aggregate.

If we were to dig into my pocket again, we would see that: my left = (.25 * 2) + (.10 * 2) + (.01 * 3) = 50 + 20 + 3 = 73 cents or $0.73 and my right = (.25 * 1) + (.10 * 3) + (.05 * 2) = 25 + 30 + 10 = 65 cents or $0.65.

The sum of left and right = 73 + 65 = 138.  The sum of my left pocket and my right pocket is 138.  Let's prove that by running a query:

SELECT SUM(amount) sum_amount, COUNT(amount) cnt_amount, 'LEFT' tb_name
  FROM LEFT_TABLE
UNION
SELECT SUM(amount) sum_amount, COUNT(amount) cnt_amount, 'RIGHT' tb_name
  FROM RIGHT_TABLE;

edb=# SELECT SUM(amount) sum_amount, COUNT(amount) cnt_amount, 'LEFT' tb_name
edb-#   FROM LEFT_TABLE
edb-# UNION
edb-# SELECT SUM(amount) sum_amount, COUNT(amount) cnt_amount, 'RIGHT' tb_name
edb-#   FROM RIGHT_TABLE;

 sum_amount | cnt_amount | tb_name
------------+------------+---------
       0.65 |          6 | RIGHT
       0.73 |          7 | LEFT

(2 rows)

edb=#

That gives me a row for each table but how do I get a final sum?  There are several ways to do it but I am going to do it this way:

SELECT SUM(sum_amount) sum_amount, SUM(cnt_amount) cnt_amount, tb_name
  FROM (
    SELECT SUM(amount) sum_amount, COUNT(amount) cnt_amount, 'LEFT' tb_name
      FROM LEFT_TABLE
    UNION
    SELECT SUM(amount) sum_amount, COUNT(amount) cnt_amount, 'RIGHT' tb_name
      FROM RIGHT_TABLE ) as all_records;

If I run this, I get an error:

ERROR:  column "all_records.tb_name" must appear in the GROUP BY clause or be used in an aggregate function

The aggregate functions require an additional keyword if your select contains any columns not being aggregated.  Think of it this way, if we are trying to get a single row, which tb_name would we expect to see in our records?

We can change our query to this:

SELECT SUM(sum_amount) sum_amount, SUM(cnt_amount) cnt_amount, tb_name
  FROM (
    SELECT SUM(amount) sum_amount, COUNT(amount) cnt_amount, 'LEFT' tb_name
      FROM LEFT_TABLE
    UNION
    SELECT SUM(amount) sum_amount, COUNT(amount) cnt_amount, 'RIGHT' tb_name
      FROM RIGHT_TABLE ) as all_records
  GROUP BY tb_name;

edb=# SELECT SUM(sum_amount) sum_amount, COUNT(cnt_amount) cnt_amount, tb_name
edb-#   FROM (
edb(#     SELECT SUM(amount) sum_amount,
edb(#            COUNT(amount) cnt_amount,
edb(#            'LEFT' tb_name
edb(#       FROM LEFT_TABLE
edb(#     UNION
edb(#     SELECT SUM(amount) sum_amount,
edb(#            COUNT(amount) cnt_amount,
edb(#            'RIGHT' tb_name
edb(#       FROM RIGHT_TABLE ) as all_records
edb-#   GROUP BY tb_name;

 sum_amount | cnt_amount | tb_name
------------+------------+---------
       0.65 |          6 | RIGHT
       0.73 |          7 | LEFT

(2 rows)

edb=#

The GROUP BY tells the SQL engine to do a break on each change of tb_name and return a row for each distinct tb_name.  When you do a GROUP BY, you are implicitly doing a sort.

I am now doing a SUM of the COUNT.  The inner query is counting for us.  If I were to count again, I would get 2 as my result.  Not what I'm looking for.

Of course, even though that works, we're back to where we were with our first query.  If you remove tb_name from the query (from the select clause) and remove the GROUP BY altogether, the result will give us what we've been looking for.

SELECT SUM(sum_amount) sum_amount, SUM(cnt_amount) cnt_amount
  FROM (
    SELECT SUM(amount) sum_amount, COUNT(amount) cnt_amount, 'LEFT' tb_name
      FROM LEFT_TABLE
    UNION
    SELECT SUM(amount) sum_amount, COUNT(amount) cnt_amount, 'RIGHT' tb_name
      FROM RIGHT_TABLE ) as all_records;

edb=# SELECT SUM(sum_amount) sum_amount, SUM(cnt_amount) cnt_amount
edb-#   FROM (
edb(#     SELECT SUM(amount) sum_amount, COUNT(amount) cnt_amount, 'LEFT' tb_name
edb(#       FROM LEFT_TABLE
edb(#     UNION
edb(#     SELECT SUM(amount) sum_amount, COUNT(amount) cnt_amount, 'RIGHT' tb_name
edb(#       FROM RIGHT_TABLE ) as all_records;

 sum_amount | cnt_amount
------------+------------
       1.38 |         13

(1 row)

edb=#

Can you modify the inner query so that you get the same results but you don't use a UNION?

The last SELECT keyword that I am going to show you is the HAVING keyword.  HAVING is like the WHERE clause but it is applied to the GROUP BY instead of to the select.

For example, if I wanted to do my earlier query but only return the row that was equal to 7, I could rewrite it this way:

SELECT SUM(sum_amount) sum_amount, SUM(cnt_amount) cnt_amount, tb_name
  FROM (
    SELECT SUM(amount) sum_amount, COUNT(amount) cnt_amount, 'LEFT' tb_name
      FROM LEFT_TABLE
    UNION
    SELECT SUM(amount) sum_amount, COUNT(amount) cnt_amount, 'RIGHT' tb_name
      FROM RIGHT_TABLE ) as all_records
  GROUP BY tb_name
  HAVING SUM(cnt_amount) = 7;

edb=# SELECT SUM(sum_amount) sum_amount, COUNT(cnt_amount) cnt_amount, tb_name
edb-#   FROM (
edb(#     SELECT SUM(amount) sum_amount,
edb(#            COUNT(amount) cnt_amount,
edb(#            'LEFT' tb_name
edb(#       FROM LEFT_TABLE
edb(#     UNION
edb(#     SELECT SUM(amount) sum_amount,
edb(#            COUNT(amount) cnt_amount,
edb(#            'RIGHT' tb_name
edb(#       FROM RIGHT_TABLE ) as all_records
edb-#   GROUP BY tb_name
edb-#     HAVING SUM(cnt_amount) = 7;

 sum_amount | cnt_amount | tb_name
------------+------------+---------
       0.73 |          7 | LEFT

(1 row)

edb=#

The SQL engine ran the SELECT and FROM to create the results set, it then aggregated the results using the GROUP BY and then it applied the HAVING to the final result set.  For performance reasons, if you have an item that you can restrict via the WHERE or via the HAVING, it is usually a better practice to restrict it in the WHERE clause.

That's it for the SELECT statement.  Now we'll start manipulating data and we're going to begin with the INSERT command.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.