SQL server show count 0 when no record is found

Question

I'm trying to make this query show 0 in the "nb" field when there are no record found for a specific date. Right now the output is 0 row, unless the date i specify exists in the "Commande" table.

SELECT      
  isnull(COUNT(*), 0) as nb, 
  CONVERT(date, c.Date_commande) as Date_commande, 
  f.Code_fournis
FROM        Commande c
LEFT JOIN   Fournisseur f
ON          c.Code_fournis = f.Code_fournis
WHERE       f.Code_fournis = 'XNZ'
AND         Convert(date, c.Date_commande) = '2015-10-28'
GROUP BY    CONVERT(date, c.Date_commande), f.Code_fournis
ORDER BY    c.date_commande desc

There isn't a NULL, there is no row at all. That is because your query doesn't have any rows to return because of the where clause. There a number ways you can handle this. You could use an EXISTS check and return hard coded values NOT EXISTS. — Sean Lange, Oct 29 '15 at 15:16
Just a comment, your LEFT JOIN executes as a regular inner join... Move f.Code_fournis = 'XNZ' from WHERE to ON to get true LEFT JOIN behavior. — jarlh, Oct 29 '15 at 15:16

score 0 · Answer 1 · edited May 23 '17 at 12:15

0

The problem is you can't count what isn't there. You need create a table allDates for all the dates. 100 years mean 36,500 rows so is a small table. Use this one as example

How can I generate a temporary table filled with dates in SQL Server 2000?

Then

SELECT      
  isnull(COUNT(*), 0) as nb, 
  CONVERT(date, AD.date) as Date_commande, 
  f.Code_fournis
FROM        
            allDates AD
LEFT JOIN   Commande c
       ON   AD.date = CONVERT(date, c.Date_commande)
LEFT JOIN   Fournisseur f
       ON   c.Code_fournis = f.Code_fournis
WHERE       f.Code_fournis = 'XNZ'
AND         Convert(date, c.Date_commande) = '2015-10-28'
GROUP BY    CONVERT(date, AD.date), f.Code_fournis
ORDER BY    AD.date desc

edited May 23 '17 at 12:15

Community

1
1

answered Oct 29 '15 at 15:53

Juan Carlos Oropeza

47,252
12
78
118

I created a table with dates and I tried your query and it returns no row – Sibanak Oct 29 '15 at 16:10
well I dont have your schema so I cant test it. Just add one table each time. Maybe is the join condition on `convert(date)`... Or prepare a sample schema in http://sqlfiddle.com/ – Juan Carlos Oropeza Oct 29 '15 at 16:13

Julien Vavasseur · Answer 2 · 2015-10-29T15:50:01.057

-1

Because '2015-10-28' does not exists in Commande, you need to create an row for it in a dummy table:

SELECT      
  SUM(case when c.Date_commande is null then 0 else 1 end) as nb, 
  d.Date_commande as Date_commande, 
  f.Code_fournis
FROM (values(cast('20150101' as date), 'XNZ') as d(Date_commande, Code_fournis)      
LEFT JOIN Commande c on Cast(c.Date_commande as date) = d.Date_commande
LEFT JOIN   Fournisseur f
ON          c.Code_fournis = f.Code_fournis and f.Code_fournis = d.Code_fournis
GROUP BY    d.Date_commande, f.Code_fournis
ORDER BY    d.Date_commande desc

This would work as well:

SELECT isnull(COUNT(*), 0) as nb, 
    CONVERT(date, c.Date_commande) as Date_commande, 
    f.Code_fournis
From your query
Union All
Select 0, '20150101', 'XNZ'
Where not exist (
    select 1 
    From Commande c
    LEFT JOIN   Fournisseur f
    ON c.Code_fournis = f.Code_fournis and  = d.Code_fournis
    Where f.Code_fournis = 'XNZ' 
        AND Convert(date, c.Date_commande) = '20150101'
 )

edited Oct 29 '15 at 15:50

answered Oct 29 '15 at 15:21

Julien Vavasseur

3,854
1
20
29

1

you still have a `where`clause which makes you left joins useless. – A ツ Oct 29 '15 at 15:23
there is no where clause on the 1st table – Julien Vavasseur Oct 29 '15 at 15:24
you get no row since no row satifies your where clause. – A ツ Oct 29 '15 at 15:26
there is no condition on the main table (ie. d). it will always be there – Julien Vavasseur Oct 29 '15 at 15:27
no. if there is no row in comande with the given date, it will not join with any `Fournisseur f` which in turn makes it impossible that any `Fournisseur f`would satisfy `f.Code_fournis = 'XNZ'` – A ツ Oct 29 '15 at 15:28
Have you looked at the whole query: FROM (values(cast('20150101' as date)) as d(dt) ??? There is always 1 row in this table. – Julien Vavasseur Oct 29 '15 at 15:30
Just tried your code @Julien Vavasseur , still returns no row. thanks for your help though – Sibanak Oct 29 '15 at 15:30
I forgot a cast in the 1st join on Date_commande. And if you don't move Code_Fournis to the On clause, it will not work (see jarhl comment). you can try it again – Julien Vavasseur Oct 29 '15 at 15:33
This will only work for the specific date, you will have to rewrite it for each date that you wish to check – Steve Ford Oct 29 '15 at 15:59
He said "the date i specify" so I guessed he will specify a date – Julien Vavasseur Oct 29 '15 at 16:00

score -1 · Accepted Answer · answered Dec 02 '15 at 22:23

Even though this is old and has a few downvotes, I recently solved my problem. I had to generate a table with dates.

SELECT      SUM(CONVERT(int, case(vtl.oeil) WHEN RTRIM('I') THEN 2 ELSE 1 END)) as nb, 
            CONVERT(date, c.Date_commande) as Date_commande,
            RTRIM(f.Code_fournis) AS Code_fournis
FROM        Commande c
LEFT JOIN   Fournisseur f
ON          c.Code_fournis = f.Code_fournis
WHERE       c.Code_fournis = 'XNZ'
AND         Convert(date, c.Date_commande)
BETWEEN     '2015-11-30'
AND         '2015-12-04'
GROUP BY    CONVERT(date, c.Date_commande), f.Code_fournis
UNION ALL
SELECT      '0', date, 'XNZ'
FROM        allDates
WHERE       date    
BETWEEN     '2015-11-30'
AND         '2015-12-04'
AND date NOT IN (
        SELECT      convert(date, c.Date_commande) as date
        FROM        allDates ad 
        INNER JOIN  Commande c 
        ON          ad.date = convert(date, c.Date_commande) 
        WHERE       c.Code_fournis = 'XNZ'
        and         convert(date, c.Date_commande) 
        BETWEEN     '2015-11-30'
        AND         '2015-12-04'
)
ORDER BY    c.date_commande asc

It's a bit different then what I asked for but hopefully it can help someone.

SQL server show count 0 when no record is found

3 Answers3