Is it possible to group by first characters of a column in SQL?-CodePudding

So I have the following table:

Name    Value
A          10
ABC         5
A           8
ABC         3
AB          2

And I want this result:

Name    Value
A          28    
AB         10    
ABC         8

So I don't want to group by exact match with Name, but by the starting characters. Is it possible?

CodePudding user response：

You could use UNION for each number of characters you want to group by. Something like this:

SELECT cat, SUM(val) num FROM (
  SELECT SUBSTRING(Name,1,1) cat, SUM(Value) val FROM table GROUP BY cat
  UNION ALL
  SELECT SUBSTRING(Name,1,2) cat, IF(LENGTH(Name)>1, SUM(Value), 0) val FROM table GROUP BY cat
  UNION ALL
  SELECT SUBSTRING(Name,1,3) cat, IF(LENGTH(Name)>2, SUM(Value), 0) val FROM table GROUP BY cat
) a
GROUP BY cat
ORDER BY cat

The IF-operator on the string length prevents inclusion of sums for strings shorter than the length you want to calculate on.

Result

 ────── ───── 
| cat  | num |
 ────── ───── 
| A    | 28  |
| AB   | 10  |
| ABC  | 8   |
 ────── ─────

CodePudding user response：

The query differs depending on whether or not the result includes non-existent character data (e.g. "B" does not exist for "BC").

If you use a recursive query, the result contains non-existent character data.

WITH cte as (
  SELECT SUBSTRING(Name,1,3) Name, SUM(Value) Value FROM Table1
  GROUP BY SUBSTRING(Name,1,3)
  UNION ALL
  SELECT SUBSTRING(Name,1,LEN(Name)-1), Value FROM cte
  WHERE LEN(Name)>1
)
SELECT Name, SUM(Value) FROM cte
GROUP BY Name
ORDER BY Name

If you use subqueries joined, the result doesn't contain non-existent character data.

WITH cte as (
  SELECT SUBSTRING(Name,1,3) Name, SUM(Value) Value FROM Table1
  GROUP BY SUBSTRING(Name,1,3)
)
SELECT
  t1.Name,
  SUM(t2.Value)
FROM cte t1 JOIN cte t2
  ON t1.Name=SUBSTRING(t2.Name,1,LEN(t1.Name))
GROUP BY t1.Name
ORDER BY t1.Name

db fiddle

Example with more data: db fiddle

If Name is 3 characters, SUBSTRING(Name,1,3) is just Name. See the following.

db fiddle

Example with more data: db fiddle

CodePudding user response：

With SQL Server 2022 :

SELECT LEFT("Name", "value") AS STRING, SUM("Value") AS TOTAL
FROM   SoIHaveTheFollowingTable
       CROSS APPLY GENERATE_SERIES(1, CASE WHEN LEN("Name") > 3 THEN 3 ELSE LEN("Name") END)
GROUP  BY LEFT("Name", "value")
ORDER BY STRING;

NOTE : with a case senitive database collation because of the two columns nammed "Value" and "value"... By the fact you should never use SQL identifiers that are reserved words (value, name, type, date...)

For version < 2022, use a recursive query first to generate the serie of int values