How do i calculate percentage out of sum in mysql?

Question

Ive got this simple table

browser	pageviews
safari	7.1875
ie	1.935
firefix	1.3445
safari	2.03

Im trying to calculate the percentage of the page views out of the total (in millions) for each browser.

with total as
( select sum(pageviews) as total
from pageviews )
select browser,
pageviews / total.total as share
from pageviews,
total

but im receiving this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'total as ( select sum(pageviews) as total

Any suggestion on how to calculate the percentage? Thanks!

*You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'total as ( select sum(pageviews) as total* Your MySQL version is 5.x, and it does not support CTE. Upgrade. — Akina, Mar 14 '21 at 10:24
@Akina It might be an idea to sit back and let the OP respond. Your comment is incorrect, and unhelpful. — Strawberry, Mar 14 '21 at 10:56
The error you are getting seems to indicate that WITH is not supported in your version of MySQL, what version of MySQL are you using? WITH was introduced in version 8. — Mark B, Mar 14 '21 at 12:10

Akina · Answer 1 · 2021-03-14T10:26:56.100

1

select browser, 
       pageview,
       pageview / SUM(pageview) OVER () as share
from pageviews

For MySQL 5.x use

select browser,
       pageview,
       pageview / total.total as share
from pageviews
CROSS JOIN ( select sum(pageview) as total
             from pageviews ) total

https://dbfiddle.uk/?rdbms=mysql_8.0&rdbms2=mysql_5.7&fiddle=6c188ab43ec7a81495630bcc266f46bd

edited Mar 14 '21 at 10:26

answered Mar 14 '21 at 10:14

Akina

39,301
5
14
25

You are making the question more difficult than it is. the OP made an error in his field name, and you are 'silently' ignoring that error, and bloating it with the true! statement that you cannot do `WITH` in older version of MySQL. – Luuk Mar 14 '21 at 10:32
@Luuk No. See error message in the question and my comment for it. *OP made an error in his field name* I don't know where is a typo - OP must fix it. – Akina Mar 14 '21 at 10:34
OK, but you answer is not about this problem, it is about a problem which does not exist (yet). (BTW, the downvote on your 'answer' is not mine..) – Luuk Mar 14 '21 at 10:36
*but you answer is not about this problem* Do you think that "Your version is ancient" must be written as separate answer? Really? I assume this as a typo (and more precisely like "OP have not read the documentation"). – Akina Mar 14 '21 at 10:39
Obviously the OP's MySql version is prior to 8.0+, so I don't understand the downvote here. – forpas Mar 15 '21 at 10:19

Kazi Mohammad Ali Nur Romel · Answer 2 · 2021-03-20T10:52:51.920

0

If you are using older version of MySQL then:

QUery:

select browser,
pageviews / total.total as share
from pageviews,
( select sum(pageviews) as total
from pageviews )total

Output:

browser share

safari 0.575138039579793

ie 0.15483715807683296

firefix 0.10758581745403162

safari 0.1624389848893424

db<>fiddle here

If you are using MySQL 8 then you can use window function:

select browser, pageviews / sum(pageviews)over(partition by (select 1)) as share from pageviews

DB-Fiddle Link:

create table pageviews (browser varchar(20),pageviews float);
insert into  pageviews values('safari',7.1875);
insert into  pageviews values('ie',1.935);
insert into  pageviews values('firefix',1.3445);
insert into  pageviews values('safari',2.03);

with total as
( select sum(pageviews) as total
from pageviews )
select browser,
pageviews / total.total as share
from pageviews,
total

browser share

safari 0.575138039579793

ie 0.15483715807683296

firefix 0.10758581745403162

safari 0.1624389848893424

select browser, pageviews / sum(pageviews)over(partition by (select 1)) as share from pageviews

browser share

safari 0.575138039579793

ie 0.15483715807683296

firefix 0.10758581745403162

safari 0.1624389848893424

db<>fiddle here

emphasized text

edited Mar 20 '21 at 10:52

answered Mar 14 '21 at 10:09

Kazi Mohammad Ali Nur Romel

15,301
2
13
26

Those are same in the query presented in the question. that's why it happened I think. – Kazi Mohammad Ali Nur Romel Mar 14 '21 at 10:23
@Alon Kri if any of the answer helped you to solve the problem please accept that by clicking green check. – Kazi Mohammad Ali Nur Romel Mar 19 '21 at 18:24
As discussed elsewhere, it's obvious that the OP is using an older version of MySQL. – Strawberry Mar 20 '21 at 10:25
I have added answer for older version of MySQL. – Kazi Mohammad Ali Nur Romel Mar 20 '21 at 10:41
The OP is using a version of MySQL prior to 8. – Strawberry Mar 20 '21 at 10:43
That's why I have added a section in my answer which will work even in 5.5. Thanks a lot for your suggestion. – Kazi Mohammad Ali Nur Romel Mar 20 '21 at 10:46
Why not put that bit at the top? – Strawberry Mar 20 '21 at 10:48
You are right again. I have changed order of query in my answer. You are a blessing. – Kazi Mohammad Ali Nur Romel Mar 20 '21 at 10:53

score -1 · Answer 3 · answered Mar 14 '21 at 10:29

Change you query to use correct field names (You use pageviews as column name, but the name of this column is pageview ?):

with total as
( select sum(pageview) as total
from pageviews )
select browser,
pageview / total.total as share
from pageviews,
total

browser	share
safari	0.575138039579793
ie	0.15483715807683296
firefix	0.10758581745403162
safari	0.1624389848893424

How do i calculate percentage out of sum in mysql?

3 Answers3