is there a way to make the count(*) return zero . because the default behavoir is that it will skip this value if there is no records
so a simple query like this
select userid,count(*) as count from users where userid in (select val from sometable)
group by userid
if userid 1 has no records . it wont be returned in the query
instead i want it to show zero
so
userid count
1 0
2 3
3 1
4 0
thanks in advance
hi
you can try this
select userid,count(*) as [count] from testDB where
userid in (select userid from testdb1)
group by userid
union
select userid,0 as [count] from testDB where
userid not in (select userid from testdb1)
group by userid
|||
How about this query..
Select
Val
,Count(UserId)
from
someTable A
Left Outer Join users B On A.val=B.userid
Group By
Val
|||you can also try this one..
Code Snippet
select *
into #users
from (
select 1 as userid union all
select 2 as userid union all
select 3 as userid
) users
select *
into #sometable
from (
select 1 as val
) sometable
select a.userid
, case when count(a.userid) = 1 and sum(b.sumcheck) = 0 then 0 else count(a.userid) end as count
from #users a left outer join
(
select userid
, isnull(val,0) as sumcheck
from #users left outer join
#sometable on #users.userid = #sometable.val
) b on a.userid = b.userid
group by
a.userid
drop table #users
drop table #sometable
RamezR,
Those rows like [userid] = 1, are being excluded from the result set because of the filter in the "where" clause. The is a keyword in the "group by" clause (ALL), that let you see those rows excluded by the filter in the "where" clause.
select userid,count(*) as count
from users
where userid in (select val from sometable)
group by userid ALL
go
AMB
|||when i try to run the query i get Incorrect syntax near the keyword 'ALL'.i was not aware of this "ALL" keyword but when i looked up the msdn i found that its kinda irrelvant in that case.
so far i found that the best result is achieved using the left outer join
Regards
|||deleted my previous post.. i think what hunchback meant was..
select userid,count(*) as count
from users
where userid in (select val from sometable)
group by all userid|||
rh4m1113,
Thanks for jumping in. That is exactly what I meant, but did not test it.
AMB
|||hunchback,actually i was also confused and thought only SQL 2005 had the GROUP BY ALL clause 
.. good thing i tested it.. i have actually learned from this one.. cool post hunchback..
rhamille|||
rh4m1ll3,
Glad you learned something from the post. The "left join" idea is a good one, but you have to be sure that the rows in the right side table has no duplicated rows by the columns used in the join, or you select distinct values from that table previous the join, if not you will get wrong result.
AMB
|||hunchback,true, so far on some production codes that i have used a similar approach none of them had a requirement of having duplicate records, i'd prole' be taking note of your approach just in case a similar requirement comes. it's much more cleaner and straight forward as well as maximizes the the group by clauses's potential
rhamille|||
Hi hunchback & rh4m1ll3,
Let me understand your solution, (i accept the feature GROUP BY ALL in sql server ).
But I am really confused to accept GROUP BY ALL for this requirement.
See the bellow example, are you sure GROUP BY ALL will work for this requirement?
Code Snippet
Create Table #sometable (
[val] int
);
Insert Into #sometable Values('2');
Insert Into #sometable Values('2');
Insert Into #sometable Values('2');
Insert Into #sometable Values('3');
Insert Into #sometable Values('3');
Insert Into #sometable Values('5');
Insert Into #sometable Values('5');
Create Table #users (
[UserId] int
);
Insert Into #users Values('1');
Insert Into #users Values('2');
Insert Into #users Values('3');
Insert Into #users Values('4');
Insert Into #users Values('5');
Insert Into #users Values('6');
Select
UserId
,Count(Val) Counts
from
#users B
Left Outer Join #sometable A On A.val=B.userid
Group By
UserId
/*
UserId Counts
-- --
1 0
2 3
3 2
4 0
5 2
6 0
*/
Select
UserId
,Count(*) Counts
from
#users B
Where
UserId in (Select val From #sometable)
Group By ALL
UserId
/*
UserId Counts
-- --
1 0
2 1
3 1
4 0
5 1
6 0
*/
|||mani,good and interesting point .. i have just recently realized that you might have misplaced the Val and UserId on your previous(first) post.. good approach for such requirement
rhamille|||
Manivannan.D.Sekaran,
Look at the original post and execute the stament using the sample data in your post
Select
UserId
,Count(*) Counts
from
#users
where
UserId in (select [val] from #sometable)
Group By
UserId
Result:
UserId Counts
2 1
3 1
5 1
If we want to include also the rows not matching the criteria in the "where" clause, but this time using "left join", then you need to be sure that we join to unique values from #sometable:
Select
u.UserId
,Count(s.[val]) Counts
from
#users as u
left join
(select distinct [val] from #sometable) as s
on u.UserId = s.[val]
Group By
u.UserId
Result
UserId Counts
1 0
2 1
3 1
4 0
5 1
6 0
As you can see, the result from the statement using "left join" and the statement using "GROUP BY ALL", yield same result for the rows returned by the original statement.
AMB
|||Please do NOT use GROUP BY ALL. It is a non-standard extension and will be deprecated in the future. It is marked for deprecation in SQL Serve 2005. So if you use it in your code now you may have to change it in the next version of SQL Server or the version after that.
No comments:
Post a Comment