CODEWITHSUNDEEP
sqldatabasepostgresql
pedro_lopz
pedro_lopz

Reputation: 3

SQL COUNT returns No Data when the result should be 0

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:

timestamp host current_state
1638290800 serverA 0
1638290800 serverB 0
1638290770 serverA 0
1638290770 serverB 2

TABLE B

host location
serverA Barcelona
serverB New york
serverC Barcelona
serverD New york

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:

timestamp count
1638290770 1

And I'd like something like:

timestamp count
1638290800 0
1638290770 1

Upvotes: 0

Views: 2427

Answers (2)

Bruno
Bruno

Reputation: 122719

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

Upvotes: 0

JNevill
JNevill

Reputation: 50200

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

Upvotes: 2

Related Questions