SQL: How to count occurrences based on file extension

Question

I have a query which returns a column of values:

?????? ?? ?????? ??????? ?.?..xlsx
1028-13055 Single Patient Focus Wave 3.sav
2.xlsx
2011 BBQ (13Dec2013).sav
2014 Health IT Purchasing Intentions Survey Results.xlsx
2014 Safety Training and Safety Professionals Survey.sav

How do I count the number of occurrences based on the file extensions? In the above example, we have three rows for the xlsx extension and two for the sav extension?

This would help you only the delimeter is not space but dot: http://stackoverflow.com/questions/5123585/how-to-split-a-single-column-values-to-multiple-column-values — Ziouas, Feb 27 '14 at 09:03
Why do you say that it is only 2 .sav? i count 3. is there some criterion i am missing? — Urban Björkman, Feb 27 '14 at 09:18

score 2 · Answer 1 · answered Feb 27 '14 at 09:07

2

Try this:

SELECT RIGHT(yourcolumnname,CHARINDEX('.', Reverse(yourcolumnname)) -1)
FROM yourtable

to isolate just the extension part and then count the occurrences

answered Feb 27 '14 at 09:07

Milen

8,697
7
43
57

score 2 · Answer 2 · edited Feb 27 '14 at 10:09

2

Please try:

SELECT colcnt, 
       Count(*) TotCnt 
FROM  (SELECT RIGHT(col, Charindex('.', Reverse(col)) - 1) ColCnt 
       FROM   yourtable)x 
GROUP  BY colcnt

edited Feb 27 '14 at 10:09

Hawk

5,060
12
49
74

answered Feb 27 '14 at 09:08

TechDo

18,398
3
51
64

score 2 · Accepted Answer · edited Feb 27 '14 at 10:09

2

Try this

SELECT extension, 
       Count(*) AS ExtensionCount 
FROM   (SELECT RIGHT(name, Charindex('.', Reverse(name)) - 1) AS Extension 
        FROM   files) t 
GROUP  BY extension

SQL fiddle

http://sqlfiddle.com/#!6/27269/5

edited Feb 27 '14 at 10:09

Hawk

5,060
12
49
74

answered Feb 27 '14 at 09:20

Urban Björkman

2,095
1
13
27

SQL: How to count occurrences based on file extension

3 Answers3