In this post we are going to cover SUM, AVG, MIN and MAX in Snowflake.
These four are aggregates, the same as COUNT in the previous post. SUM adds a column up. AVG averages it. MIN and MAX find the two ends of it. All four have the same shape as COUNT. Many rows go in, one row comes out.
We will run all four on one column first, and then take them apart one at a time. Two of them behave in a way that quietly puts a wrong number on a report, so we will spend most of the page on those two. AVG is not SUM divided by COUNT(*), and SUM over no rows at all does not come back as zero.
Every query below was run on a real Snowflake account, and every result you see is the output it gave back.
Episode 14 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.
- Let's create some sample data
- Let's start with all four in one query
- Now let's write that average by hand
- Let's check that on numbers we can do in our head
- Now let's try MIN and MAX on something that is not a number
- Let's run SUM and AVG on EMPLOYEES too
- Can SUM add up nothing at all?
- Let's turn that NULL into a zero
Let's create some sample data
Most of this page runs against the ORDERS table, and the column we are going to add up is AMOUNT. It is the same table the previous post counted, from the same shared sample-data script.
Copy this into a Snowflake worksheet and run it once. Then you can run every query on this page yourself.
-- Creates the ORDERS table that Level 2 works on.
-- Part of the shared sample-data script; run it once.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;
CREATE OR REPLACE TABLE ORDERS (
ORDER_ID INT,
CUSTOMER_ID INT,
ORDER_DATE DATE,
SHIP_DATE DATE,
STATUS VARCHAR,
AMOUNT NUMBER(10,2),
QUANTITY INT
);
INSERT INTO ORDERS VALUES
(1001, 101, '2023-01-12', '2023-01-15', 'SHIPPED', 4200.00, 12),
(1002, 102, '2023-01-28', '2023-02-02', 'SHIPPED', 890.50, 3),
(1003, 101, '2023-02-14', '2023-02-16', 'SHIPPED', 6150.00, 20),
(1004, 103, '2023-02-27', NULL, 'CANCELLED', 1500.00, 5),
(1005, 104, '2023-03-09', '2023-03-14', 'SHIPPED', 2310.75, 8),
(1006, 101, '2023-03-22', '2023-03-24', 'SHIPPED', 5020.00, 16),
(1007, 105, '2023-04-05', '2023-04-11', 'SHIPPED', 7890.00, 25),
(1008, 102, '2023-04-19', '2023-04-23', 'SHIPPED', 1120.00, 4),
(1009, 103, '2023-05-02', '2023-05-06', 'SHIPPED', 3340.25, 11),
(1010, 106, '2023-05-21', NULL, 'PENDING', 640.00, 2),
(1011, 101, '2023-06-08', '2023-06-10', 'SHIPPED', 8100.00, 27),
(1012, 104, '2023-06-25', '2023-06-30', 'SHIPPED', 1975.50, 7),
(1013, 105, '2023-07-11', '2023-07-15', 'SHIPPED', 6420.00, 21),
(1014, 102, '2023-07-29', '2023-08-03', 'RETURNED', 750.00, 3),
(1015, 107, '2023-08-14', '2023-08-17', 'SHIPPED', 9250.00, 30),
(1016, 103, '2023-08-30', '2023-09-04', 'SHIPPED', 2860.00, 9),
(1017, 101, '2023-09-16', '2023-09-19', 'SHIPPED', 5540.00, 18),
(1018, 106, '2023-10-03', '2023-10-08', 'SHIPPED', 1430.00, 5),
(1019, 105, '2023-10-22', '2023-10-26', 'SHIPPED', 7015.00, 23),
(1020, 107, '2023-11-07', '2023-11-10', 'SHIPPED', 10400.00, 34),
(1021, 102, '2023-11-24', '2023-11-29', 'SHIPPED', 980.00, 4),
(1022, 104, '2023-12-09', NULL, 'CANCELLED', 2250.00, 8),
(1023, 101, '2023-12-27', '2023-12-30', 'SHIPPED', 6730.00, 22),
(1024, 103, '2024-01-15', '2024-01-18', 'SHIPPED', 3910.00, 13),
(1025, 107, '2024-01-31', '2024-02-05', 'SHIPPED', 11200.00, 36),
(1026, 105, '2024-02-17', '2024-02-21', 'SHIPPED', 8340.00, 27),
(1027, 106, '2024-03-04', '2024-03-09', 'SHIPPED', 1690.00, 6),
(1028, 102, '2024-03-21', '2024-03-25', 'SHIPPED', 1240.00, 5),
(1029, 101, '2024-04-06', '2024-04-09', 'SHIPPED', 7460.00, 24),
(1030, 109, '2024-04-23', '2024-04-28', 'SHIPPED', 5180.00, 17),
(1031, 103, '2024-05-10', '2024-05-14', 'SHIPPED', 4025.00, 14),
(1032, 107, '2024-05-28', '2024-06-01', 'SHIPPED', 12850.00, 41),
(1033, 105, '2024-06-13', NULL, 'PENDING', 6900.00, 22),
(1034, 109, '2024-06-30', '2024-07-04', 'SHIPPED', 4760.00, 16),
(1035, 101, '2024-07-18', '2024-07-21', 'SHIPPED', 8820.00, 29),
(1036, 104, '2024-08-02', '2024-08-07', 'SHIPPED', 2540.00, 9),
(1037, 107, '2024-08-25', '2024-08-29', 'SHIPPED', 13100.00, 43),
(1038, 109, '2024-09-11', '2024-09-15', 'SHIPPED', 5930.00, 19),
(1039, 102, '2024-09-29', '2024-10-03', 'RETURNED', 1080.00, 4),
(1040, 101, '2024-10-16', '2024-10-19', 'SHIPPED', 9640.00, 31);
-- One deliberate NULL amount, inserted separately so it is easy to see why it
-- is there: it exists so that COUNT(*), COUNT(AMOUNT) and SUM() give
-- different answers.
INSERT INTO ORDERS VALUES
(1041, 106, '2024-11-05', NULL, 'PENDING', NULL, 3);Two details in that script decide most of what happens below, and both are deliberate. The first INSERT loads forty orders. The second loads one more, and that last one has NULL in AMOUNT. The order exists. No amount was ever recorded against it.
Episode 13 put both of those facts into real queries. COUNT(*) on this table returned 41, and COUNT(AMOUNT) returned 40. Keep that pair in mind. The middle of this page is about which of the two an average divides by.
Let's start with all four in one query
There is no reason to run these one at a time, so let's put all four on the AMOUNT column together.
SELECT SUM(AMOUNT) AS TOTAL, AVG(AMOUNT) AS AVERAGE, MIN(AMOUNT) AS SMALLEST, MAX(AMOUNT) AS LARGEST FROM ORDERS;| TOTAL | AVERAGE | SMALLEST | LARGEST |
|---|---|---|---|
| 205427.00 | 5135.67500000 | 640.00 | 13100.00 |
Four different summaries of the same rows, in one row of output. The smallest order was 640.00, the largest was 13100.00, and everything in the table totals 205427.00.
Have a look at the scale of those numbers
Forget the values for a moment and look at the decimal places. AMOUNT is declared as NUMBER(10,2), so it stores two. SUM came back with two as well. AVG came back with eight: 5135.67500000.
That is the same scale widening we saw with division back in episode 11, and it makes sense once you see AVG for what it is. An average is a division.
How far the scale widens depends on the types involved, and this course does not state a rule for it, because no rule was verified. The habit is the useful part. Do not assume an average comes back looking like the column it averaged, especially if something downstream is going to format it.
Now let's write that average by hand
An average is a total divided by a count, so we should be able to write one ourselves. Let's put three versions of the average amount in a single query, against the same rows: the AVG function, then SUM divided by COUNT(*), then SUM divided by COUNT(AMOUNT).
SELECT AVG(AMOUNT) AS AVG_FN, SUM(AMOUNT) / COUNT(*) AS SUM_OVER_COUNT_STAR, SUM(AMOUNT) / COUNT(AMOUNT) AS SUM_OVER_COUNT_COL FROM ORDERS;| AVG_FN | SUM_OVER_COUNT_STAR | SUM_OVER_COUNT_COL |
|---|---|---|
| 5135.67500000 | 5010.41463415 | 5135.67500000 |
The middle column is the odd one out. The first and the third agree exactly. The hand-built version that divides by COUNT(*) does not.
| Expression | Result | Verdict |
|---|---|---|
AVG(AMOUNT) | 5135.67500000 | Correct |
SUM(AMOUNT) / COUNT(*) | 5010.41463415 | Wrong — divides by rows, not values |
SUM(AMOUNT) / COUNT(AMOUNT) | 5135.67500000 | Correct — this is what AVG does |
So AVG divides by the number of values, not by the number of rows. The order with no amount cannot be averaged, so it is left out of the division as well as out of the total.
This is not a new rule. It is the rule from episode 13, which is that aggregates ignore NULLs, applied to both halves of the division at once. AVG gets that right on its own. An average we write by hand only gets it right if we remember to.
The two numbers differ by a little over 125 per order, and one missing AMOUNT is all it took. On a table where a column is NULL more often, the same mistake is much larger. It is also completely silent. Both numbers look perfectly reasonable, nothing errors, and no warning is issued.
Let's check that on numbers we can do in our head
The ORDERS numbers are too big to verify by eye, so let's run the same idea on the eight-row EMPLOYEES table from Level 1. The column is COMMISSION_PCT, and most employees do not have one at all.
SELECT SUM(COMMISSION_PCT) AS SUM_COMM, AVG(COMMISSION_PCT) AS AVG_COMM, COUNT(COMMISSION_PCT) AS N FROM EMPLOYEES;| SUM_COMM | AVG_COMM | N |
|---|---|---|
| 0.23 | 0.07666667 | 3 |
There are eight employees, and N is 3. Only three of them have a commission percentage. The sum is the sum of those three, 0.23.
Now look at the average. It is 0.07666667, which is 0.23 divided by 3. It is not 0.23 divided by the eight employees in the table. That is a division we can do in our head and confirm.
Said as a question rather than as a rule: AVG(COMMISSION_PCT) answers what is the average commission among the people who have one. It does not answer what is the average commission per employee. Those are different questions with different answers, and the SQL for the first one looks exactly like what somebody asking the second one would write. If you want the second, divide by COUNT(*) deliberately and leave a comment saying you meant it.
Now let's try MIN and MAX on something that is not a number
People tend to leave MIN and MAX alone because they assume both are arithmetic. They are not. They work on anything that has an order, and text has one.
Let's ask for the first and the last surname.
SELECT MIN(LAST_NAME) AS FIRST_ALPHABETICALLY, MAX(LAST_NAME) AS LAST_ALPHABETICALLY FROM EMPLOYEES;| FIRST_ALPHABETICALLY | LAST_ALPHABETICALLY |
|---|---|
| Fischer | Reid |
The first and the last surname alphabetically, from one query with no ORDER BY and no LIMIT.
Now let's do the same thing with dates
Dates have an order too. This is the shape of every what period does this data cover question you will ever be handed.
SELECT MIN(HIRE_DATE) AS EARLIEST, MAX(HIRE_DATE) AS LATEST FROM EMPLOYEES;| EARLIEST | LATEST |
|---|---|
| 2016-05-30 | 2023-02-13 |
The earliest hire date and the most recent one, in one query. Run that against a table somebody has just given you and you know its date range before you write anything that depends on it. That is a much better position than finding the range out from a report that looks wrong.
Let's run SUM and AVG on EMPLOYEES too
Nothing new in this query. It is worth running once so the numbers are familiar when they come back in the next post.
SELECT SUM(SALARY) AS PAYROLL, AVG(SALARY) AS AVG_SALARY FROM EMPLOYEES;| PAYROLL | AVG_SALARY |
|---|---|
| 796650.00 | 99581.25000000 |
Total payroll is 796650.00 across the eight employees, and the average is 99581.25000000. That is the eight decimal places again, for the same reason as before.
Both of those are single numbers for the whole table, which is as far as this level of aggregation goes so far. The interesting version of this query is the same two functions split by department, and that is exactly what GROUP BY adds in the next post.
Can SUM add up nothing at all?
This is the one worth memorising. There is no status called 'NOPE', so the WHERE clause below matches no rows whatsoever. Let's see what two aggregates do with an empty set.
SELECT SUM(AMOUNT) AS SUM_NONE, COUNT(*) AS COUNT_NONE FROM ORDERS WHERE STATUS = 'NOPE';| SUM_NONE | COUNT_NONE |
|---|---|
| NULL | 0 |
SUM returned NULL. COUNT returned 0. Two aggregates in one query, over the same empty set, disagreeing about what nothing means.
Both of them are defensible, and the reason is in the question each one is asking.
| Aggregate | The question it asks | Its answer over no rows |
|---|---|---|
COUNT(*) | How many are there? | None — which is a number, and the number is 0 |
SUM(AMOUNT) | What do these add up to? | There is nothing to add up, so there is no total — not a total of zero, no total |
Downstream it does not matter whether it is defensible. On a dashboard tile that NULL is a blank rather than a 0. And from episode 6 you already know that anything you do with a NULL gives you another NULL, so every calculation after this one is gone too.
Let's turn that NULL into a zero
COALESCE is the tool for this, and it came up in episode 6. It gives the NULL a fallback value. Let's put both versions in one query so the before and the after sit on the same row.
SELECT SUM(AMOUNT) AS RAW_SUM, COALESCE(SUM(AMOUNT), 0) AS COALESCED_SUM FROM ORDERS WHERE STATUS = 'NOPE';| RAW_SUM | COALESCED_SUM |
|---|---|
| NULL | 0.00 |
An empty result becomes a zero instead of a hole. Wrap the SUM itself, not the thing you do with it afterwards. By then the NULL has already spread.
Now let's make that zero readable
COALESCE solves the formatting problem by throwing information away. No rows matched and rows matched and totalled zero are genuinely different facts, and after COALESCE they look identical. So let's keep the count alongside the safe total.
SELECT COALESCE(SUM(AMOUNT), 0) AS SUM_SAFE, COUNT(*) AS COUNT_NONE FROM ORDERS WHERE STATUS = 'NOPE';| SUM_SAFE | COUNT_NONE |
|---|---|
| 0.00 | 0 |
Now we can read that zero. SUM_SAFE is 0.00 and COUNT_NONE is 0, so the zero means nothing matched.
If COUNT_NONE had come back as a number greater than zero with the same 0.00 total, that would mean real orders which genuinely add up to nothing. A completely different situation, and we can tell the two apart without leaving the query.
Use COALESCE deliberately, where a NULL would leak into a calculation or onto a report. Reaching for it on every aggregate out of habit is how a table that has stopped being loaded ends up showing a confident 0.00 on a dashboard instead of an obvious blank.
Key points
SUM,AVG,MINandMAXall have the same shape asCOUNT: many rows in, one row out, and all of them ignoreNULLs.AVGdivides by the count of values, not the count of rows. It isSUMoverCOUNT(column), never overCOUNT(*).- An average built by hand with
COUNT(*)comes out too low whenever the column has aNULLin it, and nothing errors or warns. - Division widens the scale:
AMOUNThas two decimal places andAVG(AMOUNT)came back with eight. Do not assume an average looks like the column it averaged. MINandMAXwork on text and dates, not just numbers. One query gives you the range of a column you have never seen.SUMover an empty set returnsNULL, whileCOUNTover the same empty set returns0. They disagree, and both are correct.COALESCE(SUM(x), 0)turns thatNULLinto a zero before it reaches a report or a calculation.- Keep a
COUNTbeside a coalesced total. Otherwise nothing matched and matched and totalled zero look exactly the same.
In the next post we will cover GROUP BY. Every aggregate so far has answered one question about a whole table. GROUP BY makes it answer the same question once per group, so eight rows go in and four rows come out.
Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the setup script above. The ORDERS and EMPLOYEES data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series, and no rule is stated for how far division widens a numeric scale, because none was verified. Function behaviour follows the Snowflake aggregate function documentation.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.