MySQL query with ranges

Question

I'm trying to simplify a set of queries into a single one an am struggling with it.

I want to collect counts of different ranges and am doing this right now:

select count(*) from items where value < 0 and id = 43;

select count(*) from items where (value >= 0 AND value <= 10) and id = 43;

select count(*) from items where (value > 10 AND value <= 20) and id = 43;

select count(*) from items where (value > 20 AND value <= 30) and id = 43;

select count(*) from items where value > 30 and id = 43;

I want to be able to do this in a single query. How can I accomplish that?

Also I need each individual queries count still, not just a total of them together.

hjpotter92 · Accepted Answer · 2014-02-11 07:41:20Z

5

SELECT
    SUM( IF(value < 0, 1, 0) ),
    SUM( IF(value BETWEEN 0 AND 10, 1, 0) ),
    SUM( IF(value BETWEEN 10 AND 20, 1, 0) ),
    SUM( IF(value BETWEEN 20 AND 30, 1, 0) ),
    SUM( IF(value > 30, 1, 0) )
FROM items
WHERE id = 43;

Give this a try

answered Feb 11, 2014 at 7:41

hjpotter92

81k36 gold badges148 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Arion · Accepted Answer · 2014-02-11 07:40:54Z

4

Maybe something like this:

SELECT 
    SUM(CASE WHEN value < 0 and id = 43 THEN 1 ELSE 0 END) AS c1
    SUM(CASE WHEN (value >= 0 AND value <= 10) and id = 43 THEN 1 ELSE 0 END) AS c2,
    SUM(CASE WHEN (value > 10 AND value <= 20) and id = 43 THEN 1 ELSE 0 END) AS c3,
    SUM(CASE WHEN (value > 20 AND value <= 30) and id = 43 THEN 1 ELSE 0 END) AS c4,
    SUM(CASE WHEN value > 30 and id = 43 THEN 1 ELSE 0 END) AS c5
FROM 
    items

answered Feb 11, 2014 at 7:40

Arion

31.3k10 gold badges74 silver badges89 bronze badges

Collectives™ on Stack Overflow

MySQL query with ranges

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related