Efficient MySQL query to compute percentage

Question

I am newbie to MySQL and coming from Oracle background. I have a SQL query that computes the total count and percentage for type in a table.

For example:

SELECT type,count(*) count_by_type ,a.tot total, (COUNT(*)*100/a.total) percent 
FROM xyz
JOIN (select count(*) tot from xyz)a
GROUP by type
HAVING type = 'abc';

This query is doing a self-join. Is there a simpler way to achieve the same?

Define "simpler" please. This query is not complicated at all. PS: why do you use `HAVING` instead of `WHERE`? — zerkms, Jul 31 '12 at 04:28
Your query is simple enough. Not every query can be written in one or two lines of code... — Jocelyn, Jul 31 '12 at 04:41
Since `type` is a field in your table, you may use `WHERE` instead of `HAVING`. It will be faster. — Jocelyn, Jul 31 '12 at 06:57

Omesh · Answer 1 · 2012-07-31T04:35:48.780

0

No need to use SELF JOIN here and HAVING clause. try it as:

SELECT COUNT(1) INTO @var_total FROM xyz;

SELECT type, count(1) AS count_by_type,
        @var_total total,
        (COUNT(1)*100/@var_total) AS percent
FROM xyz a
WHERE type = 'abc';
GROUP by type;

edited Jul 31 '12 at 04:35

answered Jul 31 '12 at 04:30

Omesh

27,801
6
42
51

Nope. This query would produce another resultset. Not an answer – zerkms Jul 31 '12 at 04:30
Your query will produce a MySQL error since `total` is not a field in table `xyz` – Jocelyn Jul 31 '12 at 04:31
Nested query in `SELECT` part is unlikely better than in `JOIN`, since it will be performed N times – zerkms Jul 31 '12 at 04:34
`count(1)` isn't better than `coun(*)` – zerkms Jul 31 '12 at 04:34
Now better, but still - why `COUNT(1)`?!?!? – zerkms Jul 31 '12 at 04:36
Using COUNT(*) or COUNT(1) is exactly the same. [Read explanation here](http://stackoverflow.com/questions/1221559/count-vs-count1) – Jocelyn Jul 31 '12 at 04:38
What you are suggesting has two queries. But the code which is going to call is PHP, can we include club multiple queries without using stored procedure and also is WHERE going to make it any faster ? – Anand Jul 31 '12 at 06:44

score 0 · Answer 2 · answered Jul 31 '12 at 04:33

0

SELECT type,count(1) count_by_type ,(select count(1) from xyz) total,
(count_by_type*100/total) percent  FROM xyz WHERE type = 'abc';

answered Jul 31 '12 at 04:33

AlexDev

4,049
31
36

I'm sure this query will be less efficient - subquery in `SELECT` clause will be performed N times – zerkms Jul 31 '12 at 04:34
Not if it is cached, but I don`t know for sure. – AlexDev Jul 31 '12 at 04:36
mysql cannot cache nested queries – zerkms Jul 31 '12 at 04:36
Your query will probably compute `total` for each record. – Jocelyn Jul 31 '12 at 04:37

Efficient MySQL query to compute percentage

2 Answers2