Problem with COUNT, SUM and LEFT JOIN

[Nisse]

I have three tables that i want to join and fetch info from with the following query:

SELECT
domain,
SUM(revenue) AS revenue,
COUNT(DISTINCT redirections_tab.id) AS redirections,
COUNT(DISTINCT visits_tab.id) AS visits
FROM domains_tab
LEFT JOIN redirections_tab ON domains_tab.id = redirections_tab.domain_id
LEFT JOIN visits_tab ON domains_tab.id = visits_tab.domain_id
WHERE
domains_tab.client_id = '523555ae3bd12f5cc3ec406a4c8da0e9'
GROUP BY
domains_tab.id

the problem is that the SUM field 'revenue' is totally wrong since there is no DISTINCT function for SUM (and even if there was,
I would want to group by that tables id and not by 'revenue 'since these field sometimes contain the same value for multiple
records...)

How can I solve this with mysql??

thanks!
/Niels Bosma

[bad76]

Hi,
why 'revenue' is totally wrong ? What is count ?
All seems good...

[tomhath]

I'm confused. You group by "id", but you don't select it. You select "domain" but you don't group by it. Which do you want?

I don't understand what you mean by this: "I would want to group by that tables id and not by 'revenue 'since these field sometimes contain the same value for multiple records". Huh?

SELECT 
domain, 
SUM(revenue) AS revenue, 
COUNT(DISTINCT redirections_tab.id) AS redirections, 
COUNT(DISTINCT visits_tab.id) AS visits 
FROM domains_tab 
LEFT JOIN redirections_tab ON domains_tab.id = redirections_tab.domain_id 
LEFT JOIN visits_tab ON domains_tab.id = visits_tab.domain_id 
WHERE 
domains_tab.client_id = '523555ae3bd12f5cc3ec406a4c8da0e9' 
GROUP BY 
domains_tab.id