CODEWITHSUNDEEP
mysql
vize
vize

Reputation: 291

Group by Row/Column Issue in MySQL

I'm trying to get a series of counts of rows based upon a GROUP BY for a column. What I've got at the moment is this:

select grouping_column,
(select count(value) from table where value IS NOT NULL and value BETWEEN 0 AND 300) AS Count_A,
(select count(value) from table where value IS NOT NULL and value BETWEEN 301 AND 600) AS Count_B,
...many more like this...
from table
group by grouping_column;

What I imagined I would get would be something like:

Grouping_column | Count_A | Count_B

With Count_A and Count_B split by each level of grouping_column. However, this doesn't happen. Instead, what I get is the levels of the grouping_column listed, along with a count of ALL values that fall within the range specified by Count_A and Count_B.

I've tried a number of different versions of this, but seem to be off the mark each time. What is it I'm doing wrong?

Upvotes: 1

Views: 176

Answers (1)

Mark Byers
Mark Byers

Reputation: 838376

Try this:

SELECT
    grouping_column,
    SUM(value BETWEEN 0 AND 300) AS Count_A,
    SUM(value BETWEEN 301 AND 600) AS Count_B,
    ...many more like this...
FROM yourtable
GROUP BY grouping_column

Upvotes: 2

Related Questions