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

Question

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?

Answer 1

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

Answer 2

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

Answer 3

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)
from grades
Group by first_name, last_name
/

but instead one should use

select first_name, last_name, avg(grade)
from grades
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)
from grades
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)
from grades
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

Answer 4

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