# MySQL connection problems

The following query gives me strange results:

```
SELECT
`Statistics`.`StatisticID`,
COUNT(`Votes`.`StatisticID`) AS `Score`,
COUNT(`Views`.`StatisticID`) AS `Views`,
COUNT(`Comments`.`StatisticID`) AS `Comments`
FROM `Statistics`
LEFT JOIN `Votes` ON `Votes`.`StatisticID` = `Statistics`.`StatisticID`
LEFT JOIN `Views` ON `Views`.`StatisticID` = `Statistics`.`StatisticID`
LEFT JOIN `Comments` ON `Comments`.`StatisticID` = `Statistics`.`StatisticID`
GROUP BY `Statistics`.`StatisticID`
LIMIT 0, 10
```

I am requesting this in a table structure, for example:

(only data related to `Statistics.StatisticID = 8`

)

### Votes

```
StatisticID
8
```

### Views

```
StatisticID
8
8
```

### Comments

```
StatisticID
8
8
8
8
8
```

Now if I run this query I get the following set of results:

```
StatisticID Score Views Comments
8 5 5 5
```

I know where 5 of them are the number of comments - and it works if I take the comment utterance. Can anyone debug this as it is out of my reach (I'm relatively new to SQL).

Thanks Ross

source to share

Assuming you have an id field or similar on votes / views / comments:

```
SELECT
`Statistics`.`StatisticID`,
COUNT(DISTINCT `Votes`.`VoteID`) AS `Score`,
COUNT(DISTINCT `Views`.`ViewID`) AS `Views`,
COUNT(DISTINCT `Comments`.`CommentID`) AS `Comments`
FROM `Statistics`
LEFT JOIN `Votes` ON `Votes`.`StatisticID` = `Statistics`.`StatisticID`
LEFT JOIN `Views` ON `Views`.`StatisticID` = `Statistics`.`StatisticID`
LEFT JOIN `Comments` ON `Comments`.`StatisticID` = `Statistics`.`StatisticID`
GROUP BY `Statistics`.`StatisticID`
LIMIT 0, 10
```

Haven't tested it but thought it should work. (We have to use a different field because statIDID will always be the same within a given group ...)

source to share

With a join like this, you will duplicate the data as many times as you find in other tables. It is okay if there is only one matching row in each table.

Run this query without grouping and you will get an idea of why you get the same result across all counts. However, I would assume that you get 10 as the count for each field (1 * 2 * 5) If you want to solve this problem, you need to call a subquery for each account.

SELECT s.`StatisticID`, (SELECT COUNT (*) FROM Votes WHERE Votes.StatisticID = s.StatisticID) AS Score, (SELECT COUNT (*) FROM Views WHERE Views.StatisticID = s.StatisticID) AS Views, (SELECT COUNT (*) FROM Comments WHERE Comments.StatisticID = s.StatisticID) AS Comments, FROM `Statistics` s LIMIT 0, 10

There are some performance issues if the output is large. You can optimize it a bit by joining one of the tables, but I'm not sure the queryparser will be smart enough to only run once for each grouped item. I hope so. Otherwise, you can split it into different queries.

source to share