Question 112990 in Mysql Question posted in
The official MySQL documentation can be found here.

How can I have multiple mysql aggregate columns

I have the table structure below
enter image description here

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

enter image description here

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
Tags:

2

Answers


  1. 0

    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);
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search