| SQL Having | SQL Group By | Examples of SQL Group By and SQL Having |
It is known that Null <> Null. When two different NULL values are compared, the result is NULL (not TRUE), i.e. the two NULL values are not considered to be equal. Applying the same rule to the GROUP BY clause would force SQL to place each row with a NULL grouping column into a separate group by itself.
But creating a separate group for every row with a NULL in a grouping column is confusing and of no useful value, so designers wrote the SQL standard such that NULL values are considered equal for the purposes of a GROUP BY clause. Therefore, if two rows have NULL values in the same grouping columns and matching values in the remaining non-NULL grouping columns, the DBMS will group the rows together.
Simply put if the grouping column contains more than one null value, the null values are put into a single group.
For example, the grouped query:
SELECT A, B, SUM(amount_purchased) AS 'C'
FROM customers
GROUP BY A, B
ORDER BY A, B
will display a results table similar to
| A | B | C |
|---|---|---|
| NULL | NULL | 61438.0000 |
| NULL | 101 | 196156.0000 |
| AZ | NULL | 75815.0000 |
| AZ | 103 | 36958.0000 |
| CA | 101 | 78252.0000 |
| LA | NULL | 181632.0000 |
for CUSTOMERS that contain the following rows.
| A | B | amount_purchased |
|---|---|---|
| NULL | NULL | 45612.00000 |
| NULL | NULL | 15826.00000 |
| NULL | 101 | 45852.0000 |
| NULL | 101 | 74815.0000 |
| NULL | 101 | 75489.0000 |
| AZ | NULL | 75815.0000 |
| AZ | 103 | 36958.0000 |
| CA | 101 | 78252.0000 |
| LA | NULL | 96385.0000 |
| LA | NULL | 85247.0000 |
No comments:
Post a Comment