SQL Server SUM is returning null, it should return 0

Question

My query returns null if the columns are empty. It should return 0 so I can compute it inside my codes.

I've search through the web and found the "COALESCE" and "ISNULL" I am using SQL Server 2012 what should I use? and I tried the ISNULL and its not working in my query. This is my code.

 SqlCommand cmd2 = new SqlCommand("SELECT ISNULL(SUM (Male) as Male, SUM(Female) as Female ,SUM(Pax) as Pax,SUM(Single) as Single,SUM(Married) as Married,SUM(Students) as Students,SUM(Elementary) as Elementary,SUM(Highschool) as Highschool,SUM(College) as College,SUM(PWD) as PWD, SUM([AR Users]) as ARUsers,SUM([12 Below]) as age1,SUM([13-21]) as age2,SUM([22-35]) as age3,SUM([36-50]) as age4,SUM([51-65]) as age5,SUM([65 Above]) as age6 ,0) FROM  [tbl_ForeignVisit] where [Date Added] >= '"
            + dateTimePicker1.Value.Date.ToShortDateString() + "' AND  [Date Added] <='" + dateTimePicker2.Value.Date.ToShortDateString() + "'  ;", connection);

Some sample data would help to show others what your query is supposed to be doing. — Tim Biegeleisen, Sep 15 '18 at 11:12
You should (must!) use parameters rather than string concatenation. The above code will not work if the SQL Server has a different date format to the machine running the code. https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection shows you how to fix this. — mjwills, Sep 15 '18 at 11:38

D-Shih · Answer 1 · 2018-09-15T11:44:45.727

1

I think you need to use ISNULL or COALESCE for each SUM colunms instead of only one.

and your SQL query string have SQL injection problem, please use parameters instead of Connect SQL syntax

string sqlQuery = @"SELECT 
    ISNULL(SUM(Male),0) as Male, 
    ISNULL(SUM(Female),0)  as Female ,
    ISNULL(SUM(Pax),0)  as Pax,
    ISNULL(SUM(Single),0)  as Single,
    ISNULL(SUM(Married),0)  as Married,
    ISNULL(SUM(Students),0)  as Students,
    ISNULL(SUM(Elementary),0)  as Elementary,
    ISNULL(SUM(Highschool),0)  as Highschool,
    ISNULL(SUM(College),0)  as College,
    ISNULL(SUM(PWD),0)  as PWD, 
    ISNULL(SUM([AR Users]),0)  as ARUsers,
    ISNULL(SUM([12 Below]),0)  as age1,
    ISNULL(SUM([13-21]),0)  as age2,
    ISNULL(SUM([22-35]),0)  as age3,
    ISNULL(SUM([36-50]),0)  as age4,
    ISNULL(SUM([51-65]),0)  as age5,
    ISNULL(SUM([65 Above]),0)  as age6
FROM  [tbl_ForeignVisit] 
where [Date Added] >= @DateAdded"

SqlCommand cmd2 = new SqlCommand(sqlQuery);
cmd2.Parameters.Add("@DateAdded", SqlDbType.Date);
cmd2.Parameters["@DateAdded"].Value = dateTimePicker1.Value.Date;

Note

If you want to get data by compare date type. I would set SqlDbType.Date in parameter to make sure the date type can be compare.

edited Sep 15 '18 at 11:44

answered Sep 15 '18 at 11:20

D-Shih

44,943
6
31
51

This answer is just straight to the point. Thank you very much – Kcird Sep 15 '18 at 11:34
@Kcird No problem gald to help:) – D-Shih Sep 15 '18 at 11:40
1

@kcird you can saw my edit answer more explain for you – D-Shih Sep 15 '18 at 12:33
@Kcird using parameter that can easier avoid SQL injection. https://en.wikipedia.org/wiki/SQL_injection – D-Shih Sep 15 '18 at 16:05
You can saw this https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements – D-Shih Sep 15 '18 at 16:06

score 0 · Accepted Answer · answered Sep 15 '18 at 12:00

0

you need to use ISNULL for each SUM colunms.

Ex. ISNULL(SUM(Male),0) as Male

Return: 0

answered Sep 15 '18 at 12:00

ravi polara

564
3
14

SQL Server SUM is returning null, it should return 0

2 Answers2