From File location address, get data based on how many file extension are there and their count

#csv #mysql #file #extension

Problem: Assume you have million of rows, which has a address field. it contains URL like this:
*/Volumes/ Macintoh Hard Drive/Users/sahil/Desktop/some.pdf
*/Volumes/ Macintoh Hard Drive/Users/sahil/Desktop/abc.pptx
*/Volumes/ Macintoh Hard Drive/Users/sahil/Desktop/xyz.docx
*/Volumes/ Macintoh Hard Drive/Users/sahil/Desktop/some.mp4
*/Volumes/ Macintoh Hard Drive/Users/sahil/Desktop/w.mp3
*/Volumes/ Macintoh Hard Drive/Users/sahil/Desktop/q.png

Now I want to know how many
-pdfs,ppts,mp4,png and so on are present

Below query :
reverse the address, looks for '.' and extracts the string after it and names it extension and counts how many times it encountered the extension

--volume is the column where file address is saved
select REVERSE(SUBSTRING(REVERSE(volume),1,LOCATE('.',REVERSE(volume),1))) as extension, count(1) as count from loutable2 group by extension

query will return a result like this

Now if you want only pdf data

SELECT * FROM `loutable2` where REVERSE(SUBSTRING(REVERSE(volume),1,LOCATE('.',REVERSE(volume),1))) =".pdf"

now let's say you want this data in excel/csv file
All you do is make a "mysql view"

``CREATE VIEW pdf_details AS SELECT scaned,status,somecolum,volume FROMloutable2` where REVERSE(SUBSTRING(REVERSE(volume),1,LOCATE('.',REVERSE(volume),1))) =".pdf"





```sql
CREATE VIEW pdf_details AS SELECT scaned,status,somecolum,volume FROM `loutable2`  where REVERSE(SUBSTRING(REVERSE(volume),1,LOCATE('.',REVERSE(volume),1))) =".docx"

and then you can use these views and export these in excel or whatever format you want