CODEWITHSUNDEEP
mysqlsql
cwd
cwd

Reputation: 54786

How can I get the percentage of total rows with mysql for a group?

below I have a query that will get the most common user agents for a site from a table of user agents and a linked table of ip addresses:

SELECT count(*) as num, string FROM `useragent_ip`
left join useragents on useragent_id = useragents.id
group by useragent_id
having num > 2
order by num desc, string

Sometimes it will show me something like

25 Firefox
22 IE
11 Chrome
3  Safari
1  Spider 1
1  Spider 2
1  Spider 3

My question is if there is a way that since the numbers on the left represent percentages of a whole, and will grow with time, can I have part of the sql statement to show each group's percentage of the whole? So that instead of using having num > 2 then I could do something that would say get the percentage of the total rows instead of just the number of rows?

Upvotes: 3

Views: 4576

Answers (2)

Bohemian
Bohemian

Reputation: 425063

Yes you can:

select num, string, 100 * num / total as percent
from (
    select count(*) as num, string
    from useragent_ip
    left join useragents on useragent_id = useragents.id
    group by useragent_id) x
cross join (
    select count(*) as total
    from useragent_ip
    left join useragents on useragent_id = useragents.id) y
order by num desc, string;

I removed the having num > 2, because it didn't seem to make sense.

Upvotes: 4

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324650

If you add with rollup after your group by clause, then you will get a row where string is NULL and num is the total of all the browsers. You can then use that number to generate percentages.

I can't really imagine a single query doing the calculation and being more efficient than using with rollup.

Upvotes: 1

Related Questions