Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

return count 0 with mysql group by

database table like this

============================
= suburb_id   |   value
= 1           |    2
= 1           |    3
= 2           |    4
= 3           |    5

query is

SELECT COUNT(suburb_id) AS total, suburb_id 
  FROM suburbs 
 where suburb_id IN (1,2,3,4) 
GROUP BY suburb_id

however, while I run this query, it doesn't give COUNT(suburb_id) = 0 when suburb_id = 0 because in suburbs table, there is no suburb_id 4, I want this query to return 0 for suburb_id = 4, like

============================
= total       |   suburb_id
= 2           |    1
= 1           |    2
= 1           |    3
= 0           |    4
like image 215
skargor Avatar asked Aug 30 '10 02:08

skargor


People also ask

Does Count work with GROUP BY?

The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.

How do I count rows in MySQL by group?

SELECT DISTINCT ColumnName FROM TableName; Using the COUNT() function with the GROUP BY clause, then the query will produce the number of values as the count for each subgroup created based on the table column values or expressions.

Can we use GROUP BY without count?

Using COUNT, without GROUP BY clause will return a total count of a number of rows present in the table. Adding GROUP BY, we can COUNT total occurrences for each unique value present in the column.

Does count Return 0 SQL?

The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets the number of rows or non NULL column values. COUNT() returns 0 if there were no matching rows.


1 Answers

A GROUP BY needs rows to work with, so if you have no rows for a certain category, you are not going to get the count. Think of the where clause as limiting down the source rows before they are grouped together. The where clause is not providing a list of categories to group by.

What you could do is write a query to select the categories (suburbs) then do the count in a subquery. (I'm not sure what MySQL's support for this is like)

Something like:

SELECT 
  s.suburb_id,
  (select count(*) from suburb_data d where d.suburb_id = s.suburb_id) as total
FROM
  suburb_table s
WHERE
  s.suburb_id in (1,2,3,4)

(MSSQL, apologies)

like image 72
geofftnz Avatar answered Oct 19 '22 02:10

geofftnz