SQL Count to Include Zero Values

How to include zero / 0 results in COUNT aggregate?

You want an outer join for this (and you need to use person as the "driving" table)

SELECT person.person_id, COUNT(appointment.person_id) AS "number_of_appointments"
FROM person 
  LEFT JOIN appointment ON person.person_id = appointment.person_id
GROUP BY person.person_id;

The reason why this is working, is that the outer (left) join will return NULL for those persons that do not have an appointment. The aggregate function count() will not count NULL values and thus you'll not get a zero.

If you want to learn more about outer joins, here is a nice tutorial: http://sqlzoo.net/wiki/Using_Null

How to include zero-count results in query

Because you have a reference to Calls.CallDate in your HAVING clause, you are removing operators where there are no calls. If there were no calls, then CallDate would be NULL, and NULL=20170104 is not true, so these rows are excluded. You need to move this predicate to your join clause:

SELECT Operators.id, Operators.Nome, Count(Calls.OpId) AS CountCalls
FROM Operators LEFT JOIN Calls ON (Operators.id = Calls.OpId AND Calls.CallDate=20170104)
GROUP BY Calls.CallDate, Operators.id, Operators.Nome;

You also don't need to group by Calls.CallDate, since you only have one anyway, so you can just use:

SELECT Operators.id, Operators.Nome, Count(Calls.OpId) AS CountCalls
FROM Operators LEFT JOIN Calls ON (Operators.id = Calls.OpId AND Calls.CallDate=20170104)
GROUP BY Operators.id, Operators.Nome;

As an aside HAVING is the wrong operator. HAVING is for filtering aggregates, since you are not filtering an aggregate, you should simply use WHERE

SELECT Operators.id, Operators.Nome, Count(Calls.OpId) AS CountCalls
FROM Operators LEFT JOIN Calls ON Operators.id = Calls.OpId
WHERE Calls.CallDate=20170104
GROUP BY Calls.CallDate, Operators.id, Operators.Nome;

You would use HAVING if you wanted to fliter on CountCalls, e.g if you only wanted operators that had made more than 1 call you might use:

SELECT Operators.id, Operators.Nome, Count(Calls.OpId) AS CountCalls
FROM Operators LEFT JOIN Calls ON Operators.id = Calls.OpId
WHERE Calls.CallDate=20170104
GROUP BY Calls.CallDate, Operators.id, Operators.Nome
HAVING Count(Calls.OpId) > 1;

This would only return

Id Nome CountCalls
--+----+----------
 1 JDOE          2

SQL COUNT(*) in one table including zero values

When you use WHERE, you filter rows with other error codes out and they cannot be counted. Instead, select all rows as usual and LEFT JOIN to a table where zero-rows do not exist:

SELECT * 
FROM test_results t1
LEFT JOIN test_results t2 ON t2.id=t1.id
  AND 
  (t2.error1 = 1000 OR t2.error1 = 1001 OR t2.error2 = 1000 
  OR t2.error2 = 1001 OR t2.error3 = 1000 OR t2.error3 = 1001) 

As you see, the result set includes rows with errors of 1000/1001 and NULL values for other errors. Now, simply group and count them:

SELECT date(t1.date_time) AS date, t1.altpn as PN, COUNT(t2.id) AS count 
FROM test_results t1
LEFT JOIN test_results t2 ON t2.id=t1.id
  AND 
  (t2.error1 = 1000 OR t2.error1 = 1001 OR t2.error2 = 1000 
  OR t2.error2 = 1001 OR t2.error3 = 1000 OR t2.error3 = 1001) 
GROUP BY date(t1.date_time), t1.altpn 
ORDER BY date(t1.date_time), t1.altpn

http://sqlfiddle.com/#!9/9c6236/4

Include zero in COUNT with GROUP BY in MySQL

If you want all the faculties; your starting table for the JOIN should be the faculty table. Then do Left joins on the other table accordingly.

Use the following query:

SELECT `f`.`id`, `f`.`name`, COUNT(`s`.`id`) AS `total`
FROM `faculty` AS `f` 
LEFT JOIN `course` AS `c` ON `f`.`id` = `c`.`faculty_id` 
LEFT JOIN `student` AS `s` ON `c`.`id` = `s`.`course_id` 
GROUP BY `f`.`id`, `f`.`name` 
ORDER BY `f`.`name`

SQL Count to include zero values

Not so much the WHERE clause, but the GROUP BY. The query will only return data for rows that exist. That means when you're grouping by the date of the timestamp, only days for which there are rows will be returned. SQL Server can't know from context that you want to "fill in the blanks", and it wouldn't know what with.

The normal answer is a CTE that produces all the days you want to see, thus filling in the blanks. This one's a little tricky because it requires a recursive SQL statement, but it's a well-known trick:

WITH CTE_Dates AS
(
    SELECT @START AS cte_date
    UNION ALL
    SELECT DATEADD(DAY, 1, cte_date)
    FROM CTE_Dates
    WHERE DATEADD(DAY, 1, cte_date) <= @END
)
SELECT
cte_date as TIME_STAMP,
ISNULL(COUNT(HL_Logs.Time_Stamp), 0) AS counted_leads, 
FROM CTE_Dates
LEFT JOIN HL_Logs ON DATEADD(dd, 0, DATEDIFF(dd, 0, Time_Stamp)) = cte_date
WHERE Time_Stamp between @BEGIN and @END and ID_Location = @LOCATION
GROUP BY cte_date

Breaking it down, the CTE uses a union that references itself to recursively add one day at a time to the previous date and remember that date as part of the table. If you ran a simple statement that used the CTE and just selected * from it, you'd see a list of dates between start and end. Then, the statement joins this list of dates to the log table based on the log timestamp date, while preserving dates that have no log entries using the left join (takes all rows from the "left" side whether they have matching rows on the "right" side or not). Finally, we group by date and count instead and we should get the answer you want.

How to have GROUP BY and COUNT include zero sums?

Without convoluted (in my opinion) queries, your output data-set won't include dates that don't exist in your input data-set. This means that you need a data-set with the 5 days to join on to.

The simple version would be to create a table with the 5 dates, and join on that. I typically create and keep (effectively caching) a calendar table with every date I could ever need. (Such as from 1900-01-01 to 2099-12-31.)

SELECT
  calendar.calendar_date,
  Count(People.created_at)
FROM
  Calendar
LEFT JOIN
  People
    ON Calendar.calendar_date = People.created_at
WHERE
  Calendar.calendar_date >= '2012-05-01'
GROUP BY
  Calendar.calendar_date

SQL COUNT of zero values in multiple columns

One method is:

select t.id, h.num_zeros
from t cross apply
     (select count(*) as num_zeros
      from (values (hour1), (hour2), (hour3)) v(h)
      where h = 0
     ) h;

Of course a case expression is not so hard either:

select t.id,
       (case when hour1 = 0 then 1 else 0 end +
        case when hour2 = 0 then 1 else 0 end
        case when hour3 = 0 then 1 else 0 end
       ) as num_zeros

Or, if there are no negative or NULL values:

select t.id,
       (1 - sign(hour1)) + (1 - sign(hour2)) + (1 - sign(hour3)) as num_zeros
    

include 0 in count sql

You would have to create a table on the fly to contain a record per ID:

select 1 as id_col union all select 2 union all select 100;

Then you can select IDs from this table and show the count:

select all_ids.id_col, count(my_table.id_col)
from (select 1 as id_col union all select 2 union all select 3) all_ids
left join my_table on my_table.id_col = all_ids.id_col
group by all_ids.id_col;

EDIT: Now that you tagged your request with DB2: DB2 needs a dummy table to select one value from. Rather then select 1 one must write select 1 from sysibm.sysdummy1. The complete query:

select all_ids.id_col, count(my_table.id_col)
from 
(
  select 1 as id_col from sysibm.sysdummy1
  union all 
  select 2 from sysibm.sysdummy1
  union all 
  select 3 from sysibm.sysdummy1
) all_ids
left join my_table on my_table.id_col = all_ids.id_col
group by all_ids.id_col;

SQL Server : zero values with COUNT(*)

After some wriggling, I came up with this beauty containing no CASE expression:

SELECT GA.GroupAttributeID AS AttributeID, GA.Name, 
       COUNT(MA.AttributeID) AS AttributeCount
FROM GroupAttribute AS GA 
LEFT OUTER JOIN @MemberAttribute AS MA 
  ON GA.GroupAttributeID = MA.AttributeID AND MA.AttributeValue = 'True'
GROUP BY GA.GroupAttributeID, GA.Name

This takes advantage of the fact that if there are no 'True' values for a particular AttributeID, the MA.AttributeID resulting from the LEFT OUTER JOIN will be NULL. The NULL value passed into COUNT() will lead to an AttributeCount of zero. The LEFT OUTER JOIN also ensures that a row will be present in the result set for AttributeID rows with zero counts.

The assumption with this query is that all group attributes are represented in the @MemberAttribute table variable. If not, there will be rows with zero counts representing those group attributes that are absent. If this is undesirable, a WHERE clause can be added to filter them out, complicating this query. Will's solution(s) would be far more practical if this is the case.

The execution plan compares well with Will's first solution, containing one less (Compute Scalar) step. It does use a LEFT OUTER JOIN vs an INNER JOIN, however, making the two methods practically identical for this simple example. It would be interesting to see how the two solutions scale if table variable is converted to a fairly large table, instead.

Will's actual plan for his solution involving COUNT():

My actual plan:

---

Related Topics