---
title: "Problem with COUNT, SUM and LEFT JOIN"
type: "forum-thread"
url: "https://www.webforum.nu/amne/databaser-sql/80918-problem-with-count-sum-and-left-join"
topic: "Databaser & SQL"
topic_url: "https://www.webforum.nu/amne/databaser-sql"
author: "niels"
published: "2003-06-30T14:53:16.000Z"
updated: "2003-06-30T16:51:43.000Z"
replies: 4
views: 298
page: 1
pages: 1
language: "sv-SE"
site: "webForum — webforum.nu"
rights: "Upphovsrätten till varje inlägg tillhör dess författare."
attribution: "Citera som: webForum, https://www.webforum.nu/amne/databaser-sql/80918-problem-with-count-sum-and-left-join"
---

# Problem with COUNT, SUM and LEFT JOIN

## #1 — niels, 2003-06-30T14:53Z

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

Permalänk: https://www.webforum.nu/p/80918

## #2 — LarsG, 2003-06-30T15:41Z

Normally, this would be a suitable occasion to use scalar subqueries but as it is Mysql you need two queries.

Eller vill du ha ett svar på svenska?

Permalänk: https://www.webforum.nu/p/1098583

## #3 — niels, 2003-06-30T15:44Z

Så det går alltså inte att göra detta i mysql med en fråga??

Permalänk: https://www.webforum.nu/p/1098588

## #4 — LarsG, 2003-06-30T16:46Z

Om jag gissar rätt angående din datamodell och din beskrivning så. 

Du kan väl beskriva dina tabeller så...

Permalänk: https://www.webforum.nu/p/1098646

## #5 — niels, 2003-06-30T16:51Z

domains_tab är själva huvudtabellen... utifrån den vill jag sammla in data från andra tabeller där primär nyckeln i domains_tab är foreign_key.

Permalänk: https://www.webforum.nu/p/1098655

---

Tråden på webben: https://www.webforum.nu/amne/databaser-sql/80918-problem-with-count-sum-and-left-join