CODEWITHSUNDEEP
mysqljoincountleft-joininner-join
Igor
Igor

Reputation: 673

LEFT JOIN with COUNT returns unexpected value

I've two tables:

post:

id | body   | author | type | date
1  | hi!    | Igor   | 2    | 04-10
2  | hello! | Igor   | 1    | 04-10
3  | lol    | Igor   | 1    | 04-10
4  | good!  | Igor   | 3    | 04-10
5  | nice!  | Igor   | 2    | 04-10
6  | count  | Igor   | 3    | 04-10
7  | left   | Igor   | 3    | 04-10
8  | join   | Igor   | 4    | 04-10

likes:

id | author | post_id
1  | Igor   | 2
2  | Igor   | 5
3  | Igor   | 6
4  | Igor   | 8

And I want to do a query that returns the number of posts made by Igor with type 2, 3 or 4 and number of likes made by Igor too, so, I did:

SELECT COUNT(DISTINCT p.type = 2 OR p.type = 3 OR p.type = 4) AS numberPhotos, COUNT(DISTINCT l.id) AS numberLikes
FROM post p
LEFT JOIN likes l
ON p.author = l.author
WHERE p.author = 'Igor'

And the expected output is:

array(1) {
  [0]=>
  array(2) {
    ["numberPhotos"]=>
    string(1) "6"
    ["numberLikes"]=>
    string(2) "4"
  }
}

But the output is:

array(1) {
  [0]=>
  array(2) {
    ["numberPhotos"]=>
    string(1) "2"
    ["numberLikes"]=>
    string(2) "4" (numberLikes output is right)
  }
}

So, how to do this?

Upvotes: 3

Views: 54

Answers (3)

llouk
llouk

Reputation: 513

Try:

SELECT (SELECT COUNT(*) FROM post p WHERE p.type = 2 OR p.type = 3 OR p.type = 4 AND p.author = 'Igor') AS numberPhotos, (SELECT COUNT(*) FROM likes l WHERE l.auhtor = 'Igor') AS numberLikes

Upvotes: 1

FuzzyTree
FuzzyTree

Reputation: 32392

The problem is that p.type = 2 OR p.type = 3 OR p.type = 4 evaluates to either 1 or 0, so there are only 2 possible distinct counts.

To fix the issue, you can use a case statement:

COUNT(DISTINCT case when p.type in (2,3,4) then p.id end)

Upvotes: 1

Blank
Blank

Reputation: 12378

How about this.(A bit of modification from your query sql).

SELECT COUNT(DISTINCT p.type = 2) + COUNT(DISTINCT p.type = 3) + COUNT(DISTINCT p.type = 4) AS numberPhotos,       COUNT(DISTINCT l.id) AS numberLikes
FROM post p
LEFT JOIN likes l
ON p.author = l.author
WHERE p.author = 'Igor'

Upvotes: 1

Related Questions