How can I have multiple mysql aggregate columns-CodePudding

I have the table structure below

I want to be able to select the following groups

farmers who grow between 1 and 3 commodities
farmers who grow between 4 and 6 commodities
farmers who grow more than 6 commodities

My resultant query should look like below

I am completely lost as to how to go about this query. I tried

SELECT count(*) AS total,

(SELECT count(farmer_id) from farmer_commodities HAVING count(commodity_id) < 3) AS grow1_3,

(SELECT count(farmer_id) from farmer_commodities HAVING count(commodity_id) BETWEEN 4 AND 6) AS grow4_6,

(SELECT count(farmer_id) from farmer_commodities HAVING count(commodity_id) > 6) as grow_above_6

from farmer_commodities

CodePudding user response：

Try this

With data as (
SELECT farmer_id,
count(commodity_id) cnt
From table group by 
farmer_id)
Select 
count(*),
Count(distinct case when cnt
    < 3 then farmer_id end),
Count(distinct case when cnt
    BETWEEN 4 AND 6 then farmer_id end),
Count(distinct case when cnt> 6 then farmer_id end) from 
Data;

CodePudding user response：

Can you do this:

select grow1_3_commodities, grow4_6_commodities, grow_above_6_commodities from (
     (select farmer_id, count(commodity_id) as grow1_3_commodities from farmer_commodities group by farmer_id HAVING count(commodity_id) < 3) AS grow1_3
join (select farmer_id, count(commodity_id) as grow4_6_commodities from farmer_commodities group by farmer_id HAVING count(commodity_id) BETWEEN 4 AND 6) AS grow4_6
join (SELECT farmer_id, count(farmer_id) as grow_above_6_commodities from farmer_commodities group by farmer_id HAVING count(commodity_id) > 6) as grow_above_6)
where grow1_3.farmer_id = grow4_6.farmer_id and grow4_6.farmer_id = grow_above_6.farmer_id);