CODEWITHSUNDEEP
mysqlsqlaggregate-functions
Abhijeet
Abhijeet

Reputation: 12892

Optimizing percentage calculation SQL query

I have a table like this:

------------------
sl no | Error type
------------------
1     | err1
------------------
2     | err2
------------------
3     | err1
------------------
4     | err2
------------------
5     | err2
------------------
6     | err3
------------------

Result I want:

---------------------------
Error type | Count | %
---------------------------
err1       | 2     | 33.33
---------------------------
err2       | 3     | 50
---------------------------
err3       | 1     | 16.66
---------------------------

I'm using following query to get above result:

select 
    error as Error,
    count(*) as Count, 
    100* count(*)/(select count(*) from logs) as Percentage 
from logs 
group by error;

Is it optimized for the task? If not, what are some other efficient ways I can get that information?

Upvotes: 3

Views: 326

Answers (3)

Michael Petito
Michael Petito

Reputation: 13161

Before you go and try to optimize any query, you should review the execution plan. For example, in MSSQL, the query plan will look something like this:

Query Plan

You can very clearly see two index scans (top and bottom branches to the right of "Nested Loops"). The top branch is performing your count(*) aggregate to get the total number of rows in the table. This is done once, and the resulting single row containing the count is paired with each of the rows in the bottom branch. The bottom branch is performing the count aggregate for each group.

So without changing your highly readable query, you likely already have an efficient execution plan.

Upvotes: 0

Mureinik
Mureinik

Reputation: 311468

This query will perform the count(*) per row. I'd query it once, and cross join it to the individual counts:

SELECT     a.error, a.cnt, 100 * a.cnt / b.total_count AS percentage
FROM       (SELECT   error, COUNT(*) as cnt
            FROM     logs
            GROUP BY error) a
CROSS JOIN (SELECT COUNT(*) AS total_count FROM logs) b

Upvotes: 2

David Faber
David Faber

Reputation: 12485

Not sure what DBMS you're using. If your DBMS allows you to use aggregate functions as window functions (you can do this in Oracle), then you can do the following:

SELECT error, COUNT(*) AS count, 100*COUNT(*)/COUNT(*) OVER ( ) AS percentage
  FROM logs
 GROUP BY error

(Note that the OVER( ) clause is empty to indicate that the total count is what is wanted.)

Hope this helps.

Upvotes: 0

Related Questions