MYSQL sum() for distinct rows

by

I may be wrong but from what I understand

  • conversions.id is the primary key of your table conversions
  • stats.id is the primary key of your table stats

Thus for each conversions.id you have at most one links.id impacted.

You request is a bit like doing the cartesian product of 2 sets :

[clicks]
SELECT *
FROM links 
LEFT OUTER JOIN stats ON links.id = stats.parent_id 

[conversions]
SELECT *
FROM links 
LEFT OUTER JOIN conversions ON links.id = conversions.link_id

and for each link, you get sizeof([clicks]) x sizeof([conversions]) lines

As you noted the number of unique conversions in your request can be obtained via a 

count(distinct conversions.id) = sizeof([conversions])

this distinct manages to remove all the [clicks] lines in the cartesian product

but clearly

sum(conversions.value) = sum([conversions].value) * sizeof([clicks])

In your case, since

count(*) = sizeof([clicks]) x sizeof([conversions])
count(*) = sizeof([clicks]) x count(distinct conversions.id)

you have

sizeof([clicks]) = count(*)/count(distinct conversions.id)

so I would test your request with 

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;

Keep me posted !
Jerome

More Related Contents:

  1. How do I get SUM function in MySQL to return ‘0’ if no values are found?
  2. MySQL sum elements of a column
  3. Surpassing MySQL’s TIME value limit of 838:59:59
  4. How do I add two count(*) results together on two different tables?
  5. Sum until certain point – MySql
  6. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  7. Why does MYSQL higher LIMIT offset slow the query down?
  8. How to split the name string in mysql?
  9. Normalization in MYSQL
  10. Is there a REAL performance difference between INT and VARCHAR primary keys?
  11. JOIN queries vs multiple queries
  12. MySQL select statement with CASE or IF ELSEIF? Not sure how to get the result
  13. Getting around MySQL “Can’t reopen table” error
  14. Can’t Create Entity Data Model – using MySql and EF6
  15. In MySQL, can I copy one row to insert into the same table?
  16. How to copy a row and insert in same table with a autoincrement field in MySQL?
  17. MySQL: Cloning a MySQL database on the same MySql instance
  18. MySQL sharding approaches?
  19. MySQL SELECT last few days?
  20. “where 1=1” statement [duplicate]
  21. How do you debug MySQL stored procedures?
  22. Schrödingers MySQL table: exists, yet it does not
  23. MySQL Calculate Percentage
  24. ld: library not found for -lzstd while bundle install for mysql2 gem Ruby on macOS Big Sur 11.4 Apple M1
  25. How to insert selected columns from a CSV file to a MySQL database using LOAD DATA INFILE
  26. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  27. Postgres LEFT JOIN with WHERE condition
  28. MySQL – Using COUNT(*) in the WHERE clause
  29. Array data type, split string,
  30. How can I make a table in MySQL called “order”?

Leave a Comment