April 8, 2008 at 4:22 pm
Hi:
I have the following query that may return null if certain dates are selected, I want to display 0 instead of a blank but cannot figure it out:
* I removed the date code to make it easier
SELECT ISNULL(COUNT(*), '0') AS thecount
FROM dbo.table1
WHERE (dbo.table1.school = 'xxxxxxxxxxxxx')
GROUP BY dbo.table1.school
Thanks for any help you can provide!
Mitch
April 8, 2008 at 4:25 pm
I want to see those "certain dates" which will make the query return NULL.
I never saw COUNT(*) returning NULL in my life.
_____________
Code for TallyGenerator
April 8, 2008 at 9:05 pm
c
April 9, 2008 at 3:21 am
[font="Verdana"]
Mitchel T (4/8/2008)
Hi:I have the following query that may return null if certain dates are selected, I want to display 0 instead of a blank but cannot figure it out:
* I removed the date code to make it easier
SELECT ISNULL(COUNT(*), '0') AS thecount
FROM dbo.table1
WHERE (dbo.table1.school = 'xxxxxxxxxxxxx')
GROUP BY dbo.table1.school
Thanks for any help you can provide!
Mitch
Are you looking for something like this?
Select IsNull(Date, 0), Count(*) From {Table} Group By Date
Confirm on this.
Mahesh
[/font]
MH-09-AM-8694
April 9, 2008 at 4:57 am
Or this?
IF EXISTS (SELECT * FROM dbo.table1 WHERE school = 'xxxxxxxxxxxxx')
SELECT COUNT(*) AS thecount
FROM dbo.table1
WHERE (dbo.table1.school = 'xxxxxxxxxxxxx')
GROUP BY dbo.table1.school
ELSE
SELECT 0 AS thecount
If it's not one of the suggestions so far, you will get good answers quicker if you...
1. Give the DDL - e.g. CREATE TABLE t1 (c1 INT, ...)
2. Give sample data for that DDL. Choose data which will demonstrate your various scenarios.
3. Give the results you would want from that sample data
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 9, 2008 at 6:43 am
Hi:
I want to first say thank you to everyone who responded and tried to help...this is a great community!
Now I want to slap myself...the problem was I didn't need the grouping statement, that was causing the problem.
Thanks,
Mitch
April 10, 2008 at 3:11 am
Mitchel,
Below is the one which i tried my own.
Table Name: Dating
Select * from Dating
Output:
Date
----
01/JAN/2008
02/JAN/2008
03/JAN/2008
04/JAN/2008
05/JAN/2008
Test 1:-
Query:
select count(*) from Dating
where substring(Date,1,2) = '01'
Return 1.
Test 2:-
Query:
select count(*) from Dating
where substring(Date,1,2) = '06'
By Default it Returns 0 only.
I think no need to use isnull function.
karthik
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply