mysql - Distinct and joins in SQL query? -

i tryint fetch distinct esigndevicecode same device every esigndevicecode new row.

 select       count(distinct(esigndevicecode)) units,      (f.devicename) devicename,      (case when u.status=1  count(u.status) else 0  end) intit,      (case when u.status=2  count(u.status) else 0  end) ekyc,      (case when u.status=4 count(u.status)  else 0  end) esign   applicationdetails u   left join esignlogs e      on e.applicationslno=u.slno    left join fingerprintdevice f     on f.slno=e.deviceid  group e.esigndevicecode; 

your group clause wrong. try this:-

select  count(distinct(esigndevicecode)) units, (f.devicename) devicename, (case when u.status=1  count(u.status) else 0  end) intit, (case when u.status=2  count(u.status) else 0  end) ekyc, (case when u.status=4 count(u.status)  else 0  end) esign  applicationdetails u left join esignlogs e  on        e.applicationslno=u.slno   left join fingerprintdevice f on f.slno=e.deviceid   group devicename, u.status;