Issues using function FOUND_ROWS() giving wrong result with join

Question

This is my query.

SELECT SQL_CALC_FOUND_ROWS (FOUND_ROWS() ) as total, 
(SELECT GROUP_CONCAT(sp.specialization) FROM DSpecialization_Master dsp LEFT JOIN Specialization_Master sp on sp.id = dsp.specialization 
WHERE dsp.profileid = pm.id and 
(dsp.specialization = (select id from Specialization_master where specialization='Dentist' ))) as drspec , 
pm.id as profileid, pm.loginid as loginid, dam.clinicname, dam.area, dam.address, dam.pincode, dam.id as 
addressid, dam.feecharge as feecharge, pm.fname, pm.lname, pm.email, pm.mobile, pm.phone, pm.gender, pm.dob, 
pm.totexp, pm.imagepath, pm.languages, pm.statement, pm.createdby, um.profile_url, um.clinic_url,
pm.hsbit,  (SELECT GROUP_CONCAT(education) FROM DEducation_Master WHERE profileid = pm.id ) as dredu 
FROM Profile_Master pm LEFT JOIN DAddress_Master dam on dam.profileid = pm.id left join Unique_Url_Master um on 
um.clinicid =dam.id WHERE dam.city='Surat' and pm.id IN (SELECT profileid FROM DSpecialization_Master 
WHERE specialization = (select id from Specialization_master where specialization='Dentist')) ORDER BY pm.id limit 0 , 10

accepted result is total:344, instated i am getting total:1.

I have execute this query and I am getting correct result.

select SQL_CALC_FOUND_ROWS id,(FOUND_ROWS() ) as total from unique_url_master1 limit 10;

total:1313

What is wrong I am doing? FYI I can't break query i.e first execute

select SQL_CALC_FOUND_ROWS

and execute

select FOUND_ROWS()

need help so badly..

EDIT

I have changed my query and use count(*) function. Now I am getting correct total:344 but it return only one row. Now can someone guide me?

SELECT  count(*) as total_count ,
        (SELECT GROUP_CONCAT(sp.specialization) FROM DSpecialization_Master dsp LEFT JOIN Specialization_Master sp on sp.id = dsp.specialization WHERE dsp.profileid = pm.id and (dsp.specialization = (select id from Specialization_master where specialization='Dentist' ))) as drspec, 
        pm.id as profileid, 
        pm.loginid as loginid, 
        dam.clinicname, 
        dam.area, 
        dam.address, 
        dam.pincode, 
        dam.id as addressid, 
        dam.feecharge as feecharge, 
        pm.fname, 
        pm.lname, 
        pm.email, 
        pm.mobile, 
        pm.phone, 
        pm.gender, 
        pm.dob, 
        pm.totexp, 
        pm.imagepath, 
        pm.languages, 
        pm.statement, 
        pm.createdby, 
        um.profile_url, 
        um.clinic_url, 
        pm.hsbit,  
        (SELECT GROUP_CONCAT(education) FROM DEducation_Master WHERE profileid = pm.id ) as dredu 
FROM Profile_Master pm 
LEFT JOIN DAddress_Master dam on dam.profileid = pm.id 
LEFT JOIN Unique_Url_Master um on um.clinicid =dam.id 
WHERE dam.city='Surat' 
AND pm.id IN (SELECT profileid FROM DSpecialization_Master WHERE specialization = (select id from Specialization_master where specialization='Dentist')) 
ORDER BY pm.id 
LIMIT 0, 10;

I have already working query in MSSQL with use of COUNT(*) OVER(), because of requirement change I have to convert MSSQL query into MYSQl. the result I am accepting this like this

Jester · Accepted Answer · 2016-04-16T13:50:28.327

You're using an aggregating function COUNT without n GROUP BY this will always result in one row. Definition of GROUP BY.

It would be much better to just use 2 separate queries to get what you want, one you select all the other variables and in one you only select the count

All rows+variables

SELECT  (SELECT GROUP_CONCAT(sp.specialization) FROM DSpecialization_Master dsp LEFT JOIN Specialization_Master sp on sp.id = dsp.specialization WHERE dsp.profileid = pm.id and (dsp.specialization = (select id from Specialization_master where specialization='Dentist' ))) as drspec, 
    pm.id as profileid, 
    pm.loginid as loginid, 
    dam.clinicname, 
    dam.area, 
    dam.address, 
    dam.pincode, 
    dam.id as addressid, 
    dam.feecharge as feecharge, 
    pm.fname, 
    pm.lname, 
    pm.email, 
    pm.mobile, 
    pm.phone, 
    pm.gender, 
    pm.dob, 
    pm.totexp, 
    pm.imagepath, 
    pm.languages, 
    pm.statement, 
    pm.createdby, 
    um.profile_url, 
    um.clinic_url, 
    pm.hsbit,  
    (SELECT GROUP_CONCAT(education) FROM DEducation_Master WHERE profileid = pm.id ) as dredu 
FROM Profile_Master pm 
LEFT JOIN DAddress_Master dam on dam.profileid = pm.id 
LEFT JOIN Unique_Url_Master um on um.clinicid =dam.id 
WHERE dam.city='Surat' 
AND pm.id IN (SELECT profileid FROM DSpecialization_Master WHERE specialization = (select id from Specialization_master where specialization='Dentist')) 
ORDER BY pm.id 
LIMIT 0, 10;

Count:

SELECT  count(*) as total_count,
FROM Profile_Master pm 
LEFT JOIN DAddress_Master dam on dam.profileid = pm.id 
LEFT JOIN Unique_Url_Master um on um.clinicid =dam.id 
WHERE dam.city='Surat' 
AND pm.id IN (SELECT profileid FROM DSpecialization_Master WHERE specialization = (select id from Specialization_master where specialization='Dentist')) 
ORDER BY pm.id 
LIMIT 0, 10;

If you really want it like your image in one query, you have to do the following:

SELECT T1.overall_count, T2.*
FROM (SELECT COUNT(*) AS overall_count
    FROM Profile_Master pm 
    LEFT JOIN DAddress_Master dam on dam.profileid = pm.id 
    LEFT JOIN Unique_Url_Master um on um.clinicid =dam.id 
    WHERE dam.city='Surat' 
    AND pm.id IN (SELECT profileid FROM DSpecialization_Master WHERE specialization = (select id from Specialization_master where specialization='Dentist')) 
    ORDER BY pm.id 
    LIMIT 0, 10) AS T1
JOIN ((SELECT GROUP_CONCAT(sp.specialization) FROM DSpecialization_Master dsp LEFT JOIN Specialization_Master sp on sp.id = dsp.specialization WHERE dsp.profileid = pm.id and (dsp.specialization = (select id from Specialization_master where specialization='Dentist' ))) as drspec, 
    pm.id as profileid, 
    pm.loginid as loginid, 
    dam.clinicname, 
    dam.area, 
    dam.address, 
    dam.pincode, 
    dam.id as addressid, 
    dam.feecharge as feecharge, 
    pm.fname, 
    pm.lname, 
    pm.email, 
    pm.mobile, 
    pm.phone, 
    pm.gender, 
    pm.dob, 
    pm.totexp, 
    pm.imagepath, 
    pm.languages, 
    pm.statement, 
    pm.createdby, 
    um.profile_url, 
    um.clinic_url, 
    pm.hsbit,  
    (SELECT GROUP_CONCAT(education) FROM DEducation_Master WHERE profileid = pm.id ) as dredu 
FROM Profile_Master pm 
LEFT JOIN DAddress_Master dam on dam.profileid = pm.id 
LEFT JOIN Unique_Url_Master um on um.clinicid =dam.id 
WHERE dam.city='Surat' 
AND pm.id IN (SELECT profileid FROM DSpecialization_Master WHERE specialization = (select id from Specialization_master where specialization='Dentist')) 
ORDER BY pm.id 
LIMIT 0, 10) AS T2

COUNT is an aggregating function, it will aggregate all the rows you have into one row. what is the result you're trying to get? if you want both all the results and the query you'll have to do 2 queries (or do 1 query but do 2 subqueries to get the same thing). If you'd change your query to have nice indentation it will be a lot more readable, also making an sql fiddle helps people to figure out what is wrong. — Jester, Apr 13 '16 at 12:47
@Archish I updated my answer, i think you could most likely simplify this query a lot more but without know the database structure i'm not going to try. hope this helps you — Jester, Apr 13 '16 at 13:30

Issues using function FOUND_ROWS() giving wrong result with join

1 Answers1