Browser statistics from MySQL?

[schilly]

If I have a table that logs users with their browser info, what's the easiest command to be able to sort this info based on operating system?

 

Can you use LIKE in a GROUP BY command?

 

ex. SELECT count(id) as hits, operating_system FROM log GROUP BY operating_system LIKE "Windows"

 

Would I have to use multiple queries for each OS (Mac, Windows, Mobile, etc) or can this by done in one query?

 

Thanks.

[kickstart]

Hi

 

Don't think you could do that.

 

You could probably use a case statement to create a pseudo column that contains the operating system

 

SELECT id, (CASE WHEN operating_system LIKE "%Windows%" THEN "Bills System" WHEN operating_system LIKE "%Mac%" THEN "Steves System" ELSE "Another System") AS operating_system FROM log 

 

You could then use that as a subselect in a select to do the counts.

 

SELECT operating_system, COUNT(id)
FROM (SELECT id, (CASE WHEN operating_system LIKE "%Windows%" THEN "Bills System" WHEN operating_system LIKE "%Mac%" THEN "Steves System" ELSE "Another System") AS operating_system FROM log) Deriv1
GROUP BY operating_system

 

All the best

 

Keith

[schilly]

Thanks Keith. I'll give it a try.

 

GROUP BY operating_system LIKE "%Macintosh%" works but it seems like you would have to do a query for each OS you wanted as each time it returns 2 rows(LIKE and NOT LIKE)