MySQL query using group by clause returns more rows than without group by clause

Question

Without group by clause, only 1 record is returned; among them, the required number of records is returned

SELECT 
PM.id AS id, PM.email AS email, PM.name AS name, PM.mobile AS mobile, 
PM.country AS country, PM.status AS status, PM.cdate AS joined_date, 
PM.details_status AS details_status, PM.freeze AS freeze, PM.blacklist AS blacklist, 
PM.blacklist_remark AS blacklist_remark, PM.blacklist_adate AS blacklist_adate, 
MA.m_app_id AS app_id, MA.full_name AS replace_name, MAX(MA.ldate) AS ldate, 
MA.service_type AS svc_type 

FROM whatever_db.tw_person_merchant PM 
    LEFT JOIN (
        whatever_db.tw_person_merchant PMM
        INNER JOIN
        whatever_db.tw_merchant_application_details MA 
        ON PMM.app_id = MA.m_app_id
        INNER JOIN 
        whatever_db.tw_merchant_application MAP 
        ON MA.m_app_id = MAP.id
        AND MAP.status NOT IN ('10' , '60')
        AND MA.id = (SELECT max(id) FROM whatever_db.tw_merchant_application_details WHERE m_app_id = PMM.app_id )
    )  ON PM.app_id = MA.m_app_id 

WHERE PM.details_status IN ('90', '0')
AND PM.blacklist = 0 
AND PM.name != ''

-- **here, without the group by clause, the result is just 1 record
GROUP BY PM.id 
ORDER BY PM.id DESC

I am testing whether the sql script returns relevant merchants, but only 1 record is displayed in the middle. After repeated trials, the inserted "group by" solved this problem. But this "group by" is not summarizing, but showing more results than without it. Unexpected results and such a betrayal of reason.

Answer 1

When you use GROUP BY, the query results have one row for each distinct value of the GROUP BY expression. In your case, each value of PM.id takes one row. The results of an aggregate function such as MAX() are applied to the subset of rows in each group associated with a given value.

If you don't use GROUP BY, the result is actually a group, so the query only returns one row. Any aggregate function is applied to the entire set of rows.

https://dev.mysql.com/doc/refman /8.0/en/select.html Deep in that long page contains this sentence: