How to get the desired output by using min function in mysql? -

i using below query in mysql

select count(c.id), u.id users u  inner join zipcode_users_1_c zu on zu.zipcode_users_1users_idb = u.id  left join zipcode z on z.id = zu.zipcode_users_1zipcode_ida  left join branches b on u.branch_id = b.id  left join branches_loan_1_c bl on b.id = bl.branches_loan_1branches_ida  left join loan l on l.id = bl.branches_loan_1loan_idb  left join accounts_loan_1_c al on l.id = al.accounts_loan_1loan_idb  left join accounts on a.id = accounts_loan_1accounts_ida  left join cases_cstm cc on u.id = cc.user_id_c  left join cases c on cc.id_c = c.id  , u.deleted = 0 , zu.deleted = 0 , z.deleted = 0 , bl.deleted = 0 , b.deleted = 0 , and l.deleted = 0 , al.deleted = 0 , a.deleted = 0 , c.deleted = 0 l.id = '34599008-3931-a6e1-b736-5795f5e9ff0b' , z.code = a.billing_address_postalcode , u.status = 'active' group u.id 

and getting result

+-------------+--------------------------------------+ | count(c.id) | id                                   | +-------------+--------------------------------------+ |           4 | 40fc0f12-97a5-cd06-cf42-57961a48ee28 | |           0 | 84ce03df-dc38-6920-e4d4-543792545b5a | |           3 | 88f6a311-9f15-c50e-cf09-5753e764dd18 | |           0 | 46ce0bd2-dc28-6921-24d4-547792515346 | +-------------+--------------------------------------+ 

now, here want record count(c.id) low i.e. in case there 2 records count(c.id) 0. looking forward use min() function getting "invalid use of group function" error, if use min() function count() function. so, kindly guide me here. thank you.

you should format query better. put join conditions on in join belong to, not this

left join cases c on cc.id_c = c.id  , u.deleted = 0 , zu.deleted = 0 , z.deleted = 0 , bl.deleted = 0 

then in where clause conditions turn left joins inner joins.

to solve problem, put query have in subquery. manual has excellent entry explains how solve problem:

quote:

the rows holding group-wise maximum of column

task: each article, find dealer or dealers expensive price.

this problem can solved subquery one:

select article, dealer, price   shop s1  price=(select max(s2.price)               shop s2               s1.article = s2.article); 

the preceding example uses correlated subquery, can inefficient (see section 13.2.10.7, “correlated subqueries”). other possibilities solving problem use uncorrelated subquery in clause or left join.

uncorrelated subquery:

select s1.article, dealer, s1.price shop s1 join (   select article, max(price) price   shop   group article) s2   on s1.article = s2.article , s1.price = s2.price; 

left join:

select s1.article, s1.dealer, s1.price shop s1 left join shop s2 on s1.article = s2.article , s1.price < s2.price s2.article null; 

the left join works on basis when s1.price @ maximum value, there no s2.price greater value , s2 rows values null.

clarification:

to illustrate more easily, create view of query this:

create view foo select count(c.id) cnt, .../*the rest of query*/ 

now, need minimum value of count()..

select min(cnt) foo; 

now want rows corresponding value.

select * foo cnt = (select min(cnt) foo);