Is there a way with MySQL to merge multiple queries to the same table to get the different results in there own rows?

Question

Here is my query:

SELECT COUNT(a.rating_id), COUNT(b.rating_id), COUNT(c.rating_id)
FROM wp_ratings a
LEFT JOIN wp_ratings b
LEFT JOIN wp_ratings c
WHERE a.rating_rating <= '5' AND a.rating_rating >= '4'
AND b.rating_rating <= '4' AND b.rating_rating >= '3'
AND c.rating_rating <= '3' AND c.rating_rating >= '0'

I am getting an error. I think my query is very self explanatory. I just don't want to do this:

SELECT COUNT(*) FROM wp_ratings WHERE rating_rating <= ‘5' AND rating_rating >= ‘4'
SELECT COUNT(*) FROM wp_ratings WHERE rating_rating <= ‘4' AND rating_rating >= ‘3'
SELECT COUNT(*) FROM wp_ratings WHERE rating_rating <= ‘3' AND rating_rating >= ‘0’

I am trying to get a query that will be as fast as possible.

So is there a way, with MySQL, to merge multiple queries to the same table to get the different results in there own rows?

UPDATE

When I do EXPLAIN I see that MySQL scans the table 3 times and that table has 15 000 rows so multiply by 15 000 you get 45 000 row scans. I want to bring it down to only 15 000 if possible.

in oracle I think there's a way to accumulate the values in a single fullscan. I don't know if you can do the same with mysql — Leo, Mar 07 '14 at 17:08
maybe using mysql variables (I am not sure, really) http://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql — Leo, Mar 07 '14 at 17:10

Ulrich Thomas Gabor · Accepted Answer · 2014-03-07T17:24:56.853

5

SELECT
SUM(IF(rating_rating <= 5 AND rating_rating >= 4, 1, 0)),
SUM(IF(rating_rating <= 4 AND rating_rating >= 3, 1, 0)),
SUM(IF(rating_rating <= 3 AND rating_rating >= 0, 1, 0))
FROM wp_ratings

Just use SUM instead of COUNT and this multiple times. You can then "count" only what you want to count.

edited Mar 07 '14 at 17:24

answered Mar 07 '14 at 17:19

Ulrich Thomas Gabor

6,584
4
27
41

score 1 · Answer 2 · answered Mar 07 '14 at 17:11

1

A naive solution could be using a UNION:

SELECT 'a' i, COUNT(rating_id) x FROM wp_ratings WHERE rating_rating <= '5' AND rating_rating >= '4'
    UNION
SELECT 'b' i, COUNT(rating_id) x FROM wp_ratings WHERE rating_rating <= '4' AND rating_rating >= '3'
    UNION
SELECT 'c' i, COUNT(rating_id) x FROM wp_ratings WHERE rating_rating <= '3' AND rating_rating >= '0'

The result of this would appear like this:

| i | x |
---------
| a | 4 |
| b | 3 |
| c | 6 |

answered Mar 07 '14 at 17:11

Chris Forrence

10,042
11
48
64

Still multiple queries to MySQL. I am trying to boost performance. – jnbdz Mar 07 '14 at 17:16
When I do EXPLAIN I see that MySQL scans the table 3 times. And it has 15 000 rows so multiply by 15 000 you get 45 000 row scans. I want to bring it down too only 15 000 if possible. – jnbdz Mar 07 '14 at 17:18

spencer7593 · Answer 3 · 2014-03-10T23:53:30.367

If the intent is to scan the table, and "count" the number of rows that meet some criteria, and get an individual "count" for each criteria, then a single query is likely going to much more efficient than merging multiple queries.

I would do something like this:

SELECT IFNULL(SUM(IF(r.rating_rating <= '5' AND r.rating_rating >= '4')),0) AS a
     , IFNULL(SUM(IF(r.rating_rating <= '4' AND r.rating_rating >= '3')),0) AS b
     , IFNULL(SUM(IF(r.rating_rating <= '3' AND r.rating_rating >= '0')),0) AS c
  FROM wp_ratings r

If there is criteria that excludes rows every "count", then we could add a WHERE clause.

For example, if we wanted every "count" to include only the rows for a particular date range, rather than specifying that criteria in every IF in the SELECT list, we could do it once in a WHERE clause, and get that criteria applied to every "count".

NOTE: this does not return the same result as the OP query.

score 0 · Answer 4 · answered Mar 07 '14 at 17:23

Another way you could accomplish this is by pivoting your results into one row:

SELECT SUM(case when rating_rating <= 5 AND rating_rating >= 4 then 1 else 0 end) as a,
       SUM(case when rating_rating < 4 AND rating_rating >= 3 then 1 else 0 end) as b,
       SUM(case when rating_rating < 3 AND rating_rating >= 0 then 1 else 0 end) as c
    FROM wp_ratings;

Note that I've modified your original query slightly; you were double-counting 4's into the 3-4 and 4-5 range.

Is there a way with MySQL to merge multiple queries to the same table to get the different results in there own rows?

4 Answers4

Linked