CODEWITHSUNDEEP
mysqlselect
Gerasimos Tsiamalos
Gerasimos Tsiamalos

Reputation: 13

mysql query - can't wrap my head around it

i have this little PHP script that tracks downloads of files and currently i am fetching the results with this query:

SELECT file, COUNT(file) AS downloads 
FROM downloads 
WHERE file LIKE 'pfx_%' 
GROUP BY file
ORDER BY downloads DESC

For every new download i create a new row where i keep the filename in one column. This brings back something like

Filename01 | 1200
Filename02 | 100 
Filename03 | 256
...
Filename0x | 932

The problem i have now is that some files have versions like Filename01_1.0 and Filename01_1.1, Filename01_1.2 etc

Is it possible to fetch these versions and count them as one Filename?

I hope it makes sense :)

Upvotes: 1

Views: 56

Answers (2)

xdazz
xdazz

Reputation: 160833

You could do like:

GROUB BY SUBSTRING_INDEX(file, '.', 1)

Upvotes: 3

fancyPants
fancyPants

Reputation: 51878

The SUBSTRING_INDEX() function is your friend:

SELECT SUBSTRING_INDEX(file, '_', 1) as file, COUNT(*) AS downloads 
FROM downloads 
WHERE file LIKE 'pfx_%' 
GROUP BY file
ORDER BY downloads DESC

Upvotes: 1

Related Questions