How To Calculate the Sum of a Value in SQL While Ensuring Each Row is Counted Only Once?

Grouping Rows in SQL Using SUM() for Distinct Values

In your MySQL query, you want to calculate the sum of a value in the conversions table while ensuring that each row is counted only once. To achieve this, you're using the DISTINCT keyword in conjunction with the SUM() function. However, you're encountering an issue where duplicate rows are inflating the SUM() result.

To resolve this issue, consider the primary key relationships in your tables. If conversions.id is the primary key of the conversions table and stats.id is the primary key of the stats table, then each conversions.id is unique to a single links.id.

Based on this understanding, you can restructure your query to calculate the sum of conversions.value for each distinct conversions.id, and then divide the result by the total count of rows within each links.id group. This will provide you with the desired sum of distinct values.

Here's the revised query:

<code class="sql">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;</code>

This query should effectively calculate the sum of conversions.value for each distinct conversions.id within each links.id group, ensuring that each row is counted only once.

The above is the detailed content of How To Calculate the Sum of a Value in SQL While Ensuring Each Row is Counted Only Once?. For more information, please follow other related articles on the PHP Chinese website!