Displaying a 0 Value when there is no Count field

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

Displaying a 0 Value when there is no Count field

Author

Topic

tjd
Starting Member

2 Posts

Posted - 2007-09-17 : 12:37:07

Hopefully this makes sense. I am new to SQL Reporting. My code is below. I am trying to get my query to display 0 when a category (prob_ctg.sym, does not have a Count value.

SELECT COUNT(*) AS Expr2, ISNULL(COUNT(*), '0') AS Total, CONVERT(varchar(20), Requestor.c_last_name) + ', ' + CONVERT(varchar(20),
Requestor.c_first_name, 112) AS Customer, Calls.ref_num, Calls.category, prob_ctg.sym, Calls.summary, DATEADD(s, Calls.open_date - 21600,
'1/1/70 12:00:00 am') AS OpenDT, DATEADD(s, Calls.close_date - 21600, '1/1/70 12:00:00 am') AS CloseDT, [Group].c_last_name AS GroupName
FROM ctct Requestor INNER JOIN
ctct [Group] INNER JOIN
call_req Calls ON [Group].id = Calls.group_id ON Requestor.id = Calls.customer INNER JOIN
int_org INNER JOIN
prob_ctg ON int_org.id = prob_ctg.organization ON Calls.category = prob_ctg.persid
WHERE (int_org.iorg_c_id LIKE '%400111%') AND (prob_ctg.del = 0) AND (DATEADD(s, Calls.open_date - 21600, CONVERT(DATETIME, '1970-01-01 00:00:00',
102)) BETWEEN @stdt AND @eddt)
GROUP BY Calls.category, prob_ctg.sym, Calls.summary, Calls.ref_num, Calls.open_date, Calls.close_date, [Group].c_last_name, int_org.iorg_name,
int_org.iorg_c_id, Requestor.c_last_name, Requestor.c_first_name

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-17 : 12:39:44

select isnull(count(*),0) as numrec
from tablex

tjd
Starting Member

2 Posts

Posted - 2007-09-17 : 13:53:46

Thank you Valter Borges for your comment. I am still unable to get it to display the zero value. I'll keep working with it.

quote:
Originally posted by ValterBorges

select isnull(count(*),0) as numrec
from tablex

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 13:58:34

Instead of COUNT(*), use COUNT(<col name here>) .

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 14:02:52

[code]SELECT COUNT(int_org.id) AS Expr2,
COUNT(*) AS Total,
CONVERT(varchar(20), Requestor.c_last_name) + ', ' + CONVERT(varchar(20), Requestor.c_first_name, 112) AS Customer,
Calls.ref_num,
Calls.category,
prob_ctg.sym,
Calls.summary,
DATEADD(s, Calls.open_date - 21600, '1/1/70 12:00:00 am') AS OpenDT,
DATEADD(s, Calls.close_date - 21600, '1/1/70 12:00:00 am') AS CloseDT,
[Group].c_last_name AS GroupName
FROM ctct Requestor
INNER JOIN call_req Calls ON Requestor.id = Calls.customer
INNER JOIN ctct [Group] ON [Group].id = Calls.group_id
LEFT JOIN prob_ctg ON Calls.category = prob_ctg.persid
AND prob_ctg.del = 0
LEFT JOIN int_org ON int_org.id = prob_ctg.organization
AND int_org.iorg_c_id LIKE '%400111%'
WHERE DATEADD(s, Calls.open_date - 21600, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) BETWEEN @stdt AND @eddt
GROUP BY Calls.category,
prob_ctg.sym,
Calls.summary,
Calls.ref_num,
Calls.open_date,
Calls.close_date,
[Group].c_last_name,
int_org.iorg_name,
int_org.iorg_c_id,
Requestor.c_last_name,
Requestor.c_first_name[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources