group by showing null/0 - 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

SQL Server 2008 Forums

Transact-SQL (2008)

group by showing null/0

Author

Topic

pecunn
Starting Member

20 Posts

Posted - 2011-05-06 : 02:36:14

Hi guys,

i have query in here showing number of result based on result category. My query looks like this.

SELECT
resultCategory,
Count(*) as 'number of result'
FROM
result
WHERE
resultcategory in ('Good mark', 'pass', 'fail') AND
resultDate BETWEEN date1 AND date2
GROUP BY
resultCategory

This will return the data as below
resultCategory | number of result
good mark | 5
pass | 1
fail | 3

however when i changed the date, which do not have student PASS on that period of time the result will showing as

resultCategory | number of result
good mark | 5
fail | 3

what i want to see is still keep showing category PASS but with 0/null value, is that possible?

resultCategory | number of result
good mark | 5
pass | 0/ null
fail | 3

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-06 : 03:47:52

SELECT DISTINCT
r1.resultCategory,
number_of_result
FROM result r1
LEFT OUTER JOIN ( SELECT resultCategory,
Count(*) as number_of_result
FROM result
WHERE resultcategory in ( 'Good mark', 'pass',
'fail' )
AND resultDate BETWEEN date1 AND date2
GROUP BY resultCategory
) r2 ON r1.resultCategory = r2.resultCategory

--------------------------
http://connectsql.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-06 : 03:51:49

[code]declare @Sample table (id int identity(1,1),txt varchar(255), ddate date)
insert @Sample
select 'A','20110505' union all
select 'A','20110506' union all
select 'B','20110401' union all
select 'C','20110505'

select * from @Sample

select
txt,
sum(case when ddate between '20110504' and '20110510' then 1 else 0 end) as count_txt
from @Sample
group by txt
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-05-07 : 13:09:10

[code]declare @resultCategores table(resultCategory varchar(9))

insert into @resultCategories values('Good mark'), ('pass'), ('fail')

SELECT
t.resultCategory,
Count(r.resultCategory) as 'number of results'
FROM
@resultCategories t
left join result r on r.resultCategory = t.resultCategory
WHERE
resultDate BETWEEN @date1 AND @date2
GROUP BY
t.resultCategory[/code]

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

pecunn
Starting Member

20 Posts

Posted - 2011-05-08 : 21:17:47

Thanks a lot lionofdezert

It works well

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources