I'm stuck trying to make this SQL query return a count of 0 if the certain coindition isn't true. Example: I've 2 tables:

TABLE A:

TABLE B

Now my SQL query looks like:

SELECT A.timestamp, Count(*)
FROM tableB as B
LEFT JOIN tableA A ON A.host = B.host
WHERE A.current_state != 0 AND B.location= 'Barcelona'
GROUP BY A.timestamp

How can I get the count of host with current state != 0 grouped by timestamp?

The result of my query is the following:

And I'd like something like:

CodePudding user response：

There's a few things to change from your very close attempt:

1. Move your where condition for barcelona into the ON for your join of that table instead. Otherwise your LEFT OUTER JOIN becomes an implicit INNER JOIN
2. Also instead of Count(*), just grab the count for b.host where a NULL for that column won't figure into the count.
3. Lastly swap the order in which you are joining these tables. You want all values from TableA and only those from TableB that meet your criteria.

SELECT A.timestamp, Count(b.host)
FROM tableA as A
LEFT JOIN tableB as B ON A.host = B.host AND B.location= 'Barcelona'
WHERE A.current_state != 0 
GROUP BY A.timestamp

CodePudding user response：

If you want to get a result row for every timestamp in Table A, whether there is a matching join entry in Table B or not, then Table A needs to be on the left on your LEFT JOIN (or use a RIGHT JOIN, less common):

SELECT A.timestamp, Count(B.*)
FROM tableA as A
LEFT JOIN tableB B
   ON A.host = B.host AND A.current_state != 0 AND B.location != 'Barcelona'
GROUP BY A.timestamp