Union of SQL requests by conditions: lost rows? Failed union?

Question

I'm trying to categorize reviews of a product to put in a chart. To summarize the results, I'm breaking the result set into four chucks:

Score >= 4.5
Score < 4.5 && Score >= 4
Score < 4 && Score >= 3
Score < 3

The SQL request can be seen below:

SELECT DISTINCT COUNT(*) AS cnt
    FROM reviews 
    WHERE score >= 4.5
    AND id = 4 
    AND live = '1'
UNION
SELECT DISTINCT COUNT(*) AS cnt
    FROM reviews 
    WHERE score >= 4 
    AND score < 4.5
    AND id = 4 
    AND live = '1'
UNION
SELECT DISTINCT COUNT(*) AS cnt
    FROM reviews
    WHERE score >= 3
    AND score < 4
    AND id = 4 
    AND live = '1'
UNION
SELECT DISTINCT COUNT(*) AS cnt
    FROM reviews 
    WHERE score < 3
    AND id = 4 
    AND live = '1'

This will then be put in an array. I've noticed the above request returns thirteen rows, or the summation of the counts equal 13. I get the below result:

10
2
1

Doing the below request...

SELECT score
    FROM reviews
    WHERE live = '1'
    AND id = 4

...returns fourteen rows. I thought perhaps the result set might be causing issue, but I don't see any. The set of scores is as follows:

5.0, 5.0, 5.0, 5.0, 4.9, 4.9, 4.9
4.8, 4.8, 4.7, 4.4, 4.2, 3.4, 2.6

I'm not sure why that lone row is missing, any ideas, perhaps my syntax is wonky? Why is the final union (where score < 3) return nothing?

If all you want is to count reviews by score range, a case group by can be much faster than unions. — Tim3880, May 23 '15 at 03:43
You can remove the DISTINCTs, since COUNT(*) without group by always returns just one row. — jarlh, May 23 '15 at 06:49

score 2 · Accepted Answer · edited May 23 '17 at 12:30

2

Note the last two query both return 1, and since UNION deletes duplicates, 10 2 1 1 -> 10 2 1. Use UNION ALL (see What is the difference between UNION and UNION ALL?).

edited May 23 '17 at 12:30

Community

1
1

answered May 23 '15 at 03:32

zw324

26,764
16
85
118

score 1 · Answer 2 · answered May 23 '15 at 06:55

Alternatively, skip the UNION, instead use CASE to do conditonional counting:

SELECT COUNT(case when Score >= 4.5 then 1 end) AS score_gt_4p5,
       COUNT(case when Score < 4.5 AND Score >= 4 then 1 end) AS score_4_to_4p5,
       COUNT(case when Score < 4   AND Score >= 3 then 1 end) AS score_3_to_4,
       COUNT(case when Score < 3 then 1 end) AS score_lt_3
FROM reviews
WHERE id = 4
  AND live = '1'

This way the table will only be read once!

Union of SQL requests by conditions: lost rows? Failed union?

2 Answers2