CODEWITHSUNDEEP
mysqlsqldatabase
user101
user101

Reputation: 155

MySQL query to get count in a range

A snapshot of my DB

enter image description here

I want to get count of pcl_No present in a specified range( SUM(tv_land + tv_imp))

I have written a query but I am getting Invalid use of group error

select
 count(CASE WHEN sum(tv_land+tv_imp) BETWEEN 0 AND 10000 THEN 1 END) as count0_10000
    from
tax_l2006
where red_date = 0
GROUP BY pcl_no

Sample Output 0-10000 10000-20000 30000-40000 >40000 2 3 1 8

Upvotes: 1

Views: 657

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I think you just want:

select count(CASE WHEN tv_land + tv_imp BETWEEN 0 AND 10000 THEN 1 END) as count0_10000
from tax_l2006
where red_date = 0;

I would write this in MySQL more simply as:

select sum( (tv_land + tv_imp) between 0 and 10000 ) as count0_10000
from tax_l2006
where red_date = 0;

MySQL treats a true boolean as "1" in a numeric context and "0" for false, so summing the boolean is counting the number of times it is true.

You can follow the same pattern for the rest of the columns.

If pcl_no is not unique and you need to sum the values, then you would use a subquery:

select sum(total between 0 and 10000) as count0_10000
from (select pcl_no, sum(tv_land + tv_imp) as total
      from tax_l2006
      where red_date = 0
      group by pcl_no
     ) p;

However, your data suggests that the column is unique, so the additional aggregation is not necessary.

Upvotes: 2

Related Questions