Display Zero by Using Count(*) If No Result Returned for a Particular Case

Display zero by using count(*) if no result returned for a particular case

I would insert the cities into a temporary table, then do a LEFT JOIN with the grouping query as follows:

CREATE TABLE #cities (edition_id INT, city VARCHAR(16))
INSERT INTO #cities VALUES(6, 'DELHI')
INSERT INTO #cities VALUES(50, 'AHMEDABAD')
INSERT INTO #cities VALUES(4, 'HYDERABAD')
INSERT INTO #cities VALUES(25, 'KOLKATA')
INSERT INTO #cities VALUES(51, 'BANGALORE')
INSERT INTO #cities VALUES(5, 'MUMBAI')
INSERT INTO #cities VALUES(24, 'CHENNAI')

select 
    c.city 'City', 
    ISNULL(t.Total, 0) 'Total'
from 
    #cities c
    LEFT JOIN (
        SELECT 
            edition_id, count(*) as Total 
        #tmptab1 
        GROUP BY edition_id
    ) AS t
    ON c.edition_id = t.edition_id

drop table #tmptab1
drop table #cities

BTW, it would make sense to have #cities as a normal table so that you don't need to create it everytime the query runs.

Return zero if no results using COUNT-GROUP BY

Assuming you have some data in the table for the months you want, you can switch to conditional aggregation:

select datename(month, date_occu) as MonthName, 
       datepart(month, date_occu) as MonthNumber,
       sum(case when agency = 'WCSO' and offense = 'DEATH INVESTIGATION' then 1 else 0 end) as Quantity
from crimes.rms4gis.dbo.tmprms4gisall 
where datediff(month, date_occu, getdate()) between 1 and 3 
group by datename(month, date_occu), datepart(month, date_occu)
order by MonthNumber;

There are other methods that involve generating the three months involved. However, that requires a somewhat more complex query. The above is a simple solution that should work in this -- and many other -- situations.

SQL-Query return zero count if no records are found

Taking a look at the documentation, you can use ISNULL:

ISNULL ( check_expression , replacement_value )

SELECT ISNULL(COUNT(comment), 0)) AS total
FROM dbo.omment
WHERE resp = MMColParam2 

showing Zero if sql count is NULL

I'm not entirely sure I understand the question, but I think you want this:

select count(codes.lcfruh) as front_lcfruh,
       dienstplan.kw,
       dienstplan.datum
from dienstplan
  left join codes on dienstplan.schicht = codes.lcfruh and codes.lcfruh <> ''
  left join personal on personal.perso_id = dienstplan.perso_id
and   personal.status = 'rezeption'
and   dienstplan.kw = $kw
group by dienstplan.datum, dienstplan.kw

If schicht comes from dienstplan there will always be a row for that (as that is the driving table). If I understand you correctly you want a 0 if no matching rows are found. Therefor you need to count the joined table.

Edit:

The condition where codes.lcfruh != '' turns the outer join back into an inner join because any "outer" row will have lcfruh as NULL and any comparison with NULL yields "unknown" and therefor the rows are removed from the final result. If you want to exclude rows in the codes table where the lcfruh has an empty string, you need to move that condition into the JOIN clause (see above).

And two more things: get used to prefixing your columns in a query with more than one table. That avoids ambiguity and makes the query more stable against changes. You should also understand the difference between number literals and string literals 1 is a number '1' is a string. It's a bad habit to use string literals where numbers are expected. MySQL is pretty forgiving as it always try to "somehow" work but if you ever user other DBMS you might get errors you don't understand.

Additionally your usage of group by is wrong and will lead to "random" values being returned. Please see these blog posts to understand why:

• http://rpbouman.blogspot.de/2007/05/debunking-group-by-myths.html
• http://www.mysqlperformanceblog.com/2006/09/06/wrong-group-by-makes-your-queries-fragile/

Every other DBMS will reject your query the way it is written now (and MySQL will as well in case you turn on a more ANSI compliant mode)

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

MySql count() to return 0 if no records found

There is no record for the month of January that is why you are getting no result. One solution that works is by joining a subquery with contains list of months that you want to be shown on the list.

SELECT count(b.id) as totalRec
FROM   (
            SELECT 'January' mnth
            UNION ALL
            SELECT 'February' mnth
            UNION ALL
            SELECT 'March' mnth
        ) a
        LEFT JOIN post b
            ON a.mnth = DATE_FORMAT(b.date, '%M') AND
               year(b.date) =  '2013' AND 
               DATE_FORMAT(b.date, '%M') IN ('January', 'February', 'March') 
GROUP  BY year(b.date)-month(b.date) 
ORDER  BY b.date ASC

• SQLFiddle Demo

OUTPUT

╔══════════╗
║ TOTALREC ║
╠══════════╣
║        0 ║
║        7 ║
║        9 ║
╚══════════╝

Select CASE WHEN ALIAS with COUNT not returning ZERO Values

To get all file statuses, you would need to have a table with these statuses. If not available, you can produce it inline with a subquery.

Secondly, the condition on the user's team should be part of the join condition, otherwise (when you put it in the where clause) it will turn the outer join into an inner join.

After comments about the "records" count, it anyway made no sense anymore to use an outer join. I would suggest a union for that category instead:

SELECT     FileStatus,
           count(DISTINCT U.id) as users
FROM       (SELECT 0 as status, 'prospects' as FileStatus
            UNION ALL SELECT 1, 'open'
            UNION ALL SELECT 2, 'open'
            UNION ALL SELECT 3, 'open'
            UNION ALL SELECT 4, 'archived') as C
INNER JOIN files_table as F 
        on F.status = C.status
INNER JOIN users_table U
        on F.user_id = U.id
       and U.team = 1  
GROUP BY   FileStatus DESC
UNION ALL
SELECT    'records',
          count(*)
FROM      users_table
WHERE     id NOT IN (SELECT user_id FROM files_table)
AND       team = 1

If you want to work with ranges, then return a low/high end in the subquery, and adapt the join condition accordingly:

SELECT     FileStatus,
           count(DISTINCT U.id) as users
FROM       (SELECT 0 as statusFrom, 0 statusTo, 'prospects' as FileStatus
            UNION ALL SELECT 1, 3, 'open'
            UNION ALL SELECT 4, 4, 'archived') as C
INNER JOIN files_table as F 
        on F.status between C.statusFrom and C.statusTo
INNER JOIN users_table U
        on F.user_id = U.id
       and U.team = 1  
GROUP BY   FileStatus DESC
UNION ALL
SELECT    'records',
          count(*)
FROM      users_table
WHERE     id NOT IN (SELECT user_id FROM files_table)
AND       team = 1

---

Related Topics