MYSQL sum() for distinct rows?

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.

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.

Use the following query: SELECT links. Id , ( SELECT COUNT(*) FROM stats WHERE links. Id = stats.

Parent_id ) AS clicks , conversions. Conversions , conversions. Conversion_value FROM links LEFT JOIN ( SELECT link_id , COUNT(id) AS conversions , SUM(conversions.

Value) AS conversion_value FROM conversions GROUP BY link_id ) AS conversions ON links. Id = conversions. Link_id ORDER BY links.

Created DESC.

I use a subquery to do this. It eliminates the problems with grouping. So the query would be something like: SELECT COUNT(DISTINCT conversions.Id) ... (SELECT SUM(conversions.

Value) FROM ....) AS Vals.

Updated question with my full query. I'm not sure how I'd integrate a subquery into what I have and how it would affect performance. – makeee Mar 12 '10 at 23:06 Subqueries normally impact performance negatively.To minimize the impact make sure any subquery is acting on an index.

– Dave Mar 15 '10 at 17:17.

How about something like this: select l. Id, count(s. Id) clicks, count(c.Id) clicks, sum(c.

Value) conversion_value from (SELECT l. Id id, l. Created created, s.

Id clicks, c. Id conversions, max(c. Value) conversion_value FROM links l LEFT JOIN stats s ON l.

Id = s. Parent_id LEFT JOIN conversions c ON l.Id = c. Link_id GROUP BY l.

Id, l. Created, s. Id, c.

Id) t order by t.created.

For an explanation of why you were seeing stupid numbers, read this. I think that Jerome has a handle on what is causing your error. Bryson's query would work, though having that subquery in the SELECT could be inefficient.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.