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?
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)
0 comments:
Post a Comment