CODEWITHSUNDEEP
mysql
Yuan
Yuan

Reputation: 472

How to simplify the query

I have a MySQL query. Is there any way to simplify the query? Thanks.

SELECT `column1`,100*CAST(COUNT(*) AS FLOAT) / (SELECT COUNT(*) FROM `table`) 
FROM `table`
GROUP BY `column1

I want to calculate the percentage of each group element in the total entry. I think I duplicate this

(SELECT COUNT(*) FROM `table`)

Is there any way I could get the total amount of entries without using the SELECT twice? I am using Mysql 8.0

Upvotes: 1

Views: 35

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

Use SUM() as analytic function here to find the total counts across the entire table:

SELECT column1, 100.0*COUNT(*) / SUM(COUNT(*)) OVER () AS pct
FROM yourTable
GROUP BY column1;

Here SUM(COUNT(*)) OVER () finds the sum of the counts of all groups, over the entire table. The "table," at the point when window functions get evaluated, is after the GROUP BY has already happened. So, it is an intermediate table of all groups.

Upvotes: 2

Related Questions