CODEWITHSUNDEEP
sqloracle-databaseselectgroup-byaggregate-functions
Keithx
Keithx

Reputation: 3148

Group by using parts of the expression

I have a table like this:

         Name              Count
23feb.01.04.(33.03.03)       1
23feb.09.(12.04.06)          9
23feb.02.09.(25.06)          1
23feb.01.02.04               2
23feb.01.02                 48
23feb.02.13                  4
23feb.01.04.(33.03.10)       1
23feb.01.04.(33.03.06)       1
23feb.02                    48
23feb.02.07                 12
23feb.01.04.(33.03.01)       1
23feb.02.02                  3
23feb.02.04                 11
23feb                      279
23feb.02.06                  3
23feb.03                    83

Is there any way to do a group by function to get aggregate result for the higher level of the Name string? So, the desirable result is like:

  Name                     Count
  23feb                     507

How can it be done?

Upvotes: 1

Views: 42

Answers (2)

Mahesh
Mahesh

Reputation: 8892

To avoid the Like wildcard you can use LEFT

  SELECT  LEFT(name,5), COUNT(*)
  FROM     my_table
  GROUP BY LEFT(name,5)

Upvotes: 1

Mureinik
Mureinik

Reputation: 311393

Any (row) expression you can query can also be a group by expression:

SELECT   SUBSTR(name, 1, INSTR(name, '.')), COUNT(*)
FROM     my_table
GROUP BY SUBSTR(name, 1, INSTR(name, '.'))

Upvotes: 2

Related Questions