Count() returning 0 for null - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count() returning 0 for null

Author

Topic

Krissi
Starting Member

9 Posts

Posted - 2014-10-08 : 15:30:05

Is there a way to allow the count () to return a 0 for the rows with a NULL value instead of not returning that row?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-08 : 15:34:56

You could use the SUM function - example:

SUM(CASE WHEN YourCol IS NULL THEN 0 else 1 END)

Or you could use a CASE expression as appropriate. If that does not solve the problem, can you post the query?

Krissi
Starting Member

9 Posts

Posted - 2014-10-08 : 15:40:43

SELECT
CONVERT(varchar,A.[MFG_DATE],101) AS ShiftDate
,A.[LINE] AS Area
,COUNT(A.[SERIAL]) AS TotalQty
,B.[Quantity] AS TargetQty

FROM [CIMHMI].[cimadm].[TnlScans] A

INNER JOIN [CIMHMI].[cimadm].[operating] B
ON A.[LINE] = B.[Line] AND A.[MFG_DATE] = b.[DATE]

WHERE [MFG_DATE] > DATEADD(month, -12, GETDATE())

group by A.[mfg_date], A.[Line], B.[Quantity]

order by A.[mfg_date] desc, A.[LINE]

When this executes, the Line# from TableA are not showing if they were shut down and no production ran. It is eliminating the rows that result in 0 from the count(). I have tried full join and outer join and it will not show them at all.

Krissi
Starting Member

9 Posts

Posted - 2014-10-08 : 15:58:55

This may need a bit more explanation. I have two tables here. Table A contains each line and all models ran on each line with a time stamp. Table B contains the target goal per line.

I need to list the date, each line, the total quantity ran that day and the target goal for that day. I need to see the target goal even if the line was shut down and didn't run.

The above query will not list all lines if the total count of units per day is NULL. I need it to pass a zero through to allow the target goal to display in the result.

Krissi

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-08 : 16:30:08

Try this to see if it gives you what you want

SELECT  CONVERT(VARCHAR(32), B.[DATE], 101) AS ShiftDate ,
        B.[LINE] AS Area ,
        COUNT(A.[SERIAL]) AS TotalQty , -- You probably should use SUM if Serial is the amount.
        B.[Quantity] AS TargetQty
FROM    [CIMHMI].[cimadm].[operating] B
        LEFT JOIN [CIMHMI].[cimadm].[TnlScans] A  ON A.[LINE] = B.[Line]
                                                      AND A.[MFG_DATE] = b.[DATE]
WHERE   b.[DATE] > DATEADD(month, -12, GETDATE())
GROUP BY B.[DATE] ,
        B.[LINE] ,
        B.[Quantity]
ORDER BY ShiftDate DESC ,
        B.[LINE]

Krissi
Starting Member

9 Posts

Posted - 2014-10-09 : 07:56:14

This does pull the values in, but the date is sorted by the month, meaning the December 2013 is showing first and it descends down based on the month......

12/31/2013 E1 0 0
12/31/2013 E4 0 0
12/31/2013 G1 0 0
12/31/2013 G2 0 175
12/31/2013 L0 0 1179
12/31/2013 L1 0 15
12/31/2013 L2 0 1698

this shows the first few records it returns...

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-09 : 08:08:53

Remove the DESC keyword from the order by clause and replace it with ASC. Default is ASC, so you could omit it as well - i.e.,

.....
ORDER BY ShiftDate ASC ,
        B.[LINE]

Krissi
Starting Member

9 Posts

Posted - 2014-10-09 : 08:43:13

this is the result when I changed that...

01/01/2014 E1 0 0
01/01/2014 E4 0 0
01/01/2014 G1 0 0
01/01/2014 G2 0 175
01/01/2014 L0 0 1179

I need it to be the newest to the oldest. In other words, the TargetQty values could be up to a week out, but the Total Quantity is what ran yesterday and historical.

Your report works great to include all the lines. When the shiftDate is set to DESC, it computes based on the month, so December 2013 is listed before October 2014.

The values for October 2013 and 2014 are listing together.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-09 : 09:25:14

This is likely because your data type for ShiftDate is not DATE or DATETIME, instead it could be a character type - such as VARCHAR. Try casting to DATETIME to see if that gives what you want

.....
ORDER BY CAST(ShiftDate AS DATETIME) DESC,
        B.[LINE]

Krissi
Starting Member

9 Posts

Posted - 2014-10-09 : 09:37:37

Msg 207, Level 16, State 1, Line 13
Invalid column name 'ShiftDate'.

Krissi
Starting Member

9 Posts

Posted - 2014-10-09 : 09:45:36

I changed the first line to the following:

SELECT CONVERT(datetime, B.[DATE], 101) AS ShiftDate ,

and i get this....

2014-10-09 00:00:00.000 E1 27 85
2014-10-09 00:00:00.000 E4 88 285
2014-10-09 00:00:00.000 G1 90 355
2014-10-09 00:00:00.000 G2 63 260
2014-10-09 00:00:00.000 L0 302 1377

Now, I just need to format out the time section. SQL Server 2005 does not recognize Date as an option.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-09 : 09:47:57

What datatype is the column B.[DATE]? Also, what format do you want for the date?

Krissi
Starting Member

9 Posts

Posted - 2014-10-09 : 10:02:23

I wanted it to show mm/dd/yyyy

Date(datetime, NOT NULL)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-09 : 10:21:53

Try using CONVERT(char(10), B.[DATE], 101)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-09 : 10:44:48

quote:
Originally posted by Krissi

Msg 207, Level 16, State 1, Line 13
Invalid column name 'ShiftDate'.

Sorry, ShiftDate is an alias in the select. You should sort by B.[DATE]

ORDER BY B.[DATE] DESC ,
        B.[LINE]

Krissi
Starting Member

9 Posts

Posted - 2014-10-09 : 11:17:25

PERFECT!!!!! THE B.[DATE] DESC worked! This is a wonderful site! Thank you so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources