CODEWITHSUNDEEP
sqlgroup-by
Manngo
Manngo

Reputation: 16311

SQL Using Ungrouped Columns in SELECT statement

I have a GROUP BY Query which appears to use non-aggregated data not in the GROUP BY clause, which I thought would not work.

I was asked to write a query which converted the following data:

| item | type | cost | category |
|------|------|------|----------|
| 1    | X    | 10   | A        |
| 1    | Y    | 20   | A        |
| 2    | X    | 30   | B        |
| 2    | Y    | 40   | B        |
| 3    | X    | 50   | C        |
| 3    | Y    | 60   | C        |
| 4    | X    | 70   | D        |
| 4    | Y    | 80   | D        |

into this:

| item | x  | y  | category |
|------|----|----|----------|
| 1    | 10 | 20 | A        |
| 2    | 30 | 40 | B        |
| 3    | 50 | 60 | C        |
| 4    | 70 | 80 | D        |

Note:

I ended up with the following solution:

SELECT
    item,
    sum(CASE WHEN type='X' THEN cost END) as X,
    sum(CASE WHEN type='Y' THEN cost END) as Y,
    category
FROM data
GROUP BY item,category;

What surprised me is that it worked. What surprised me more is that it works for PostgreSQL, MariaDB (ANSI Mode), Microsoft SQL and SQLite.

Note: - I have included category in the GROUP BY simply to allow it to appear in the SELECT clause. - I have used the sum() function, even though there will only be one value, also simply to included it in the SELECT clause.

I thought I would not be able to use type column in the SELECT column because it is not in the GROUP BY and it is not aggregated. Indeed, if I try to select it by itself, the query will fail.

The question is, how is it that I can use the type column with the CASE operator, when I can’t use it by itself?

Upvotes: 0

Views: 1348

Answers (1)

user330315
user330315

Reputation:

Your usage of the "ungrouped" columns is perfectly fine.

The rule is: "Every expression in the SELECT list must either be an aggregat function or it must part of the GROUP BY".

The column type is used inside an aggregate. sum(CASE WHEN type='X' THEN cost END) as X is not really different to sum(cost) or max(type).

This becomes more obvious if you use the standard SQL filter option:

sum(CASE WHEN type='X' THEN cost END)

is the same as: 

sum(cost) filter (where type = 'X')

However only very few DBMS support this standard.

Upvotes: 3

Related Questions