Why Does My SQL COUNT(*) Return the Total Instead of Individual Row Counts?

SQL query returns total row count instead of individual row count

When executing SQL queries, be sure to use aggregate functions such as COUNT(*) correctly to avoid incorrect results. Consider the following statement:

SELECT `ID`, `To`, `Poster`, `Content`, `Time`, ifnull(`Aura`, 0) AS `Aura`
FROM (
    SELECT DISTINCT *
    FROM messages m
    INNER JOIN
    (
        SELECT Friend2 AS Friend FROM friends WHERE Friend1 = '1'
        UNION ALL
        SELECT Friend1 AS Friend FROM friends WHERE Friend2 = '1'
    ) friends ON m.Poster = friends.`Friend`
    UNION ALL
    SELECT DISTINCT *, '1'
    FROM messages
    WHERE `Poster` = '1'
) var
LEFT JOIN
(
    SELECT `ID` AS `AuraID`, `Status` AS `AuraStatus`, COUNT(*) AS `Aura`
    FROM messages_aura
) aura ON (var.Poster = aura.AuraID AND var.ID = aura.AuraStatus)
GROUP BY `ID`, `Poster`
ORDER BY `Time` DESC
LIMIT 10;

The goal of this query is to query data from the messages and messages_aura tables and count the number of occurrences of aura messages for each user. However, the problem is in the LEFT JOIN subquery, where the GROUP BY clause is missing.

Problem: Without the GROUP BY clause, the subquery will return the total number of aura messages for all users instead of counting the number for each user. As a result, the output shows incorrect counts because it aggregates all rows instead of grouping by relevant columns.

Solution: To solve this problem, the GROUP BY clause must be added to the LEFT JOIN subquery:

LEFT JOIN
(
    SELECT `ID` AS `AuraID`, `Status` AS `AuraStatus`, COUNT(*) AS `Aura`
    FROM messages_aura
    GROUP BY AuraID, AuraStatus
) aura ON (var.Poster = aura.AuraID AND var.ID = aura.AuraStatus)

By adding GROUP BY AuraID, AuraStatus, the subquery now groups the results by these columns, ensuring that the aura count for each user is accurate.

The above is the detailed content of Why Does My SQL COUNT(*) Return the Total Instead of Individual Row Counts?. For more information, please follow other related articles on the PHP Chinese website!