How to Calculate SUM() for Distinct Rows in MySQL?

SUM() for Distinct Rows in MySQL

When utilizing the SUM() aggregation function in a MySQL query, it's crucial to consider how distinct rows are counted to ensure accurate calculations. In the provided example, the goal is to count distinct conversions and sum their values.

In the original query, the SUM(conversions.value) function counts the value for each row multiple times due to the GROUP BY on links.id. To resolve this, we need to sum conversions.value only for distinct conversions.id.

A meticulous analysis reveals that both conversions.id and stats.id are primary keys. Therefore, we can assume that for each conversions.id, there is a unique links.id.

To obtain the desired result, we can leverage the Cartesian product of two sets representing clicks and conversions. The number of unique conversions can be derived using count(distinct conversions.id).

The sum of conversion values can be corrected by multiplying it with the number of unique conversions divided by the total count: sum(conversions.value)count(distinct conversions.id)/count()

Incorporating this modification yields the following revised query:

SELECT links.id, 
   count(DISTINCT stats.id) as clicks, 
   count(DISTINCT conversions.id) as conversions, 
   sum(conversions.value)*count(DISTINCT conversions.id)/count(*) as conversion_value 
FROM links 
LEFT OUTER JOIN stats ON links.id = stats.parent_id 
LEFT OUTER JOIN conversions ON links.id = conversions.link_id 
GROUP BY links.id 
ORDER BY links.created desc;

This refined query effectively eliminates duplicate rows in the calculation of conversion_value, ensuring an accurate sum for distinct conversions.

The above is the detailed content of How to Calculate SUM() for Distinct Rows in MySQL?. For more information, please follow other related articles on the PHP Chinese website!