Mariadb 'unknown column in where clause' and get wrong result using 'count()' wi-CodePudding

I'm having problem to get the right result from the following tables

table_buyer

pkey  |  buyer_id  |  buyer_name
1     |  1         |  john
2     |  2         |  mike

table_fruit

pkey  |  buyer_id  |  fruit_name
1     |  1         |  banana
2     |  1         |  banana
3     |  1         |  apple
4     |  2         |  grape
5     |  2         |  grape
6     |  2         |  grape

then i'm trying to run the following query

select buyer_name, (select count(*) from (select fruit_name from table_fruit where buyer_id = table_buyer.buyer_id group by fruit_name) as table_group_of_fruit) as group_of_fruit from table_buyer;

expected result

buyer_name | group_of_fruit

john       | 2

mike       | 1

error message : Unknown column 'table_buyer.buyer_id' in 'where clause'

and count(*) does not count grouped fruit as a group but individually.

It seems simple and easy but i've been thinking about this for 2 days and still don't have a clue to get the right result as expected, any help will be greatly appreciated

CodePudding user response：

I think you want to aggregate by buyer and then select the distinct count of fruit:

SELECT b.buyer_name, COUNT(DISTINCT f.fruit_name) AS group_of_fruit
FROM table_buyer b
LEFT JOIN table_fruit f
    ON f.buyer_id = b.buyer_id
GROUP BY b.buyer_name;

Note that if two or more buyers could share the same name, you might want to also add the buyer_id to the select and group by clauses:

SELECT b.buyer_id, b.buyer_name, COUNT(DISTINCT f.fruit_name) AS group_of_fruit
FROM table_buyer b
LEFT JOIN table_fruit f
    ON f.buyer_id = b.buyer_id
GROUP BY b.buyer_id, b.buyer_name;