# GROUP BY using columns not in the SELECT list - when is this practical, elegant or powerful?

TL;DR - I would like examples of where using a `GROUP BY` (example shown below) using columns not in the `SELECT` list can be used to resolve SQL challenges in a [practical | elegant | powerful] way. I mean in a general way - the example demonstrated below is interesting because it demonstrates the principle (but it doesn't work!). I want working examples where use of this technique can be used to achieve something "significant".

Following up to this question, which required the counting of routes from point_x to point_y and vice-versa, taking routes between the same points to be equivalent - i.e. `A -> B` is the same for the count purposes as `B -> A`. A working general solution to that question is given there.

However, one poster (SQLRaptor) showed a solution which solved the problem for the (presumably) subset of data shown in the question, but this poster also said that their solution wasn't general and asked the OP could they see why? SQLRaptor also said this was an example of a GROUP BY using columns not in the `SELECT` list and that this could be an elegant (powerful) solution to some SQL challenges!

SQLRaptor's solution (using my own notation from answer) was:

``````SELECT  MIN(origin) AS point_1,
MAX(destination) AS point_2,
COUNT(*) AS journey_count
FROM route
GROUP BY ASCII(origin) * ASCII(destination)
ORDER BY point_1, point_2
``````

I thought the non-generality might be something to do with the multiplication (duplicates), but had no concrete proof. This transpired to be the case - see my proof here and here.

Unfortunately, this example of using a `GROUP BY` using columns not in the `SELECT` list doesn't work very well - what I would like are examples of where this technique can work well?

• Not sure that it counts, but a useful method for `GAPS and ISLAND` problem is to derive a group_number from the difference between row_number() over different partitions, where one partition is a subset of the other. The group_number is used in the group by, but not in the select – Lennart Jun 5 '18 at 3:42

I have used this technique (grouping on a column not used in the SELECT list) to combine an ordered list of values (which has gaps) into ranges of contiguous values.

First I do a rownum() on the ordered set of values. Then we diff the rownum() and the value. By grouping on the diff, we get the ranges.

``````CREATE TABLE #TMP (ID INT)

INSERT INTO #TMP
SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 6 UNION
SELECT 8 UNION
SELECT 9 UNION
SELECT 10 UNION
SELECT 15 UNION
SELECT 16 UNION
SELECT 17 UNION
SELECT 18 UNION
SELECT 19 UNION
SELECT 20

WITH RN AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY ID) AS RN, ID
FROM
#TMP
),
SRC AS
(
SELECT
RN,
ID,
ID-RN DIFF
FROM
RN
)
SELECT
MIN(ID) RANGE_START,
MAX(ID) RANGE_END,
COUNT(*) CNT_VALUES_IN_RANGE
FROM
SRC
GROUP BY
DIFF
``````
• Yep. This is typically utilized when you need to group by the result of a calculation, typically involving other rows, hence commonly used together with window functions. I will post another example later. – SQLRaptor Jun 4 '18 at 17:09

Simple example that the query fails:

``````CREATE TABLE Routes
( ID INT NOT NULL,
ORIGIN VARCHAR(2) NOT NULL,
DESTINATION VARCHAR(2) NOT NULL
);

INSERT INTO Routes
( ID, ORIGIN, DESTINATION )
VALUES
( 1, 'A', 'T' ),
( 2, 'F', 'N' ) ;
GO
``````
```2 rows affected
```
``````SELECT  MIN(origin) AS point_1,
MAX(destination) AS point_2,
COUNT(*) AS journey_count
FROM routes
GROUP BY ASCII(origin) * ASCII(destination)
ORDER BY 1, 2;
GO
``````
```point_1 | point_2 | journey_count
:------ | :------ | ------------:
A       | T       |             2
```

db<>fiddle here

Explanation of the failure:

Letter ASCII code math
A 65 = (5 * 13)
T 84 = (6 * 14)

F 70 = (5 * 14)
N 78 = (6 * 13)

AT --> 65 * 84 = (5*13) * (6*14) = (5*14) * (6*13) = 70 * 84 <-- FN

• Or, by modifying this fiddle slightly to include an `IN` (and not `=` 1 element) to get the set off all `ASCII(char_x) * ASCII(char_y)` = `ASCII(char_a) * ASCII(char_b)` where x, y, a and b are all distinct and all are ε [A-Z]. There are 5 such quadruplets, (A, T, F, N) being one! This can be seen here. It's a particular case of the proof I outlined in the question. p.s. how do you get that deadly "hover" thing to work? :-) – Vérace Jun 5 '18 at 9:09

I think the example you give is funny, but not reallly useful.

I am not sure if this is what you want.

Here is a query that I see somtimes here in stackoverflow.

``````select first_name, last_name, avg(grade)
Group by first_name, last_name
/
``````

but instead one should use

``````select first_name, last_name, avg(grade)
Group by first_name, last_name,student_id
/
``````

Of Course in most situations it it would make sense to query

``````select Student_id, first_name, last_name, avg(grade)
Group by first_name, last_name,student_id
/
``````

But if you want to print an anonyous Report the n you have to user

``````select avg(grade)
Group by student_id
/
``````

How many exercisees did the students have already solved in average?

``````select avg(no_solved_exercises
)
from
(select Count(*) no_solved_exercises
from exercises
where solved_date is not null
Group by student_id
) ex
``````

IMHO this solution only works with CHAR(1) columns.

As far as ASCII(character_expression) only returns first character ascii value, you cannot use it as a general form.

This is my example:

``````CREATE TABLE Routes
( ID INT NOT NULL,
ORIGIN VARCHAR(2) NOT NULL,
DESTINATION VARCHAR(2) NOT NULL
);

INSERT INTO Routes
( ID, ORIGIN, DESTINATION )
VALUES
( 1, 'AY', 'BW' ),
( 2, 'CA', 'DA' ),
( 3, 'BW', 'AY' ),
( 4, 'CA', 'AY' ),
( 5, 'DA', 'CA' ),
( 6, 'AZ', 'BW' ),
( 8, 'BW', 'AZ' );
``````

And this is the (wrong) result using this solution:

``````SELECT  MIN(origin) AS point_1,
MAX(destination) AS point_2,
COUNT(*) AS journey_count
FROM routes
GROUP BY ASCII(origin) * ASCII(destination)
ORDER BY 1, 2;
GO
``````
```point_1 | point_2 | journey_count
:------ | :------ | ------------:
AY      | BW      |             4
CA      | AY      |             1
CA      | DA      |             2
```

db<>fiddle here

• Yes, I did think of that, but it struck me as a bit of a trivial (not in the insulting sense :-) ) solution. I went more for the idea that you could encode routes 1 - 26 using a single letter (maybe legacy system for a small company?), but you're correct in that this is another flaw in the oversimplification of using the ASCII(blah) * ASCII(blah) "trick". However, it doesn't really answer my question about when would such a technique (and I don't just mean ASCII^2) be useful in the general sense - i.e. other scenarios with other functions. That's more what I was driving at! Thanks for replying – Vérace Jun 4 '18 at 15:07
• @Vérace you are correct. I used the ASCII trick for (over)simplification, but you can use the same technique with fancier expressions, such as converting the string to BINARY and then performing a XOR, which will work for more than one character, and solve the multiplication duplicates. It does force a binary collation as a side effect though. I'll look later for a nice challenge that uses this group by technique in my training materials, and will post an example as an answer here. – SQLRaptor Jun 4 '18 at 16:53
• @Vérace decided to open a separate thread for the challenge here: dba.stackexchange.com/questions/208731/… – SQLRaptor Jun 4 '18 at 19:39