sql noob question on COUNT

[Eytukan]

ed5756 wrote:

select userid,username,count(*) from temptable where userid=1 and score>20 group by userid

:) imagine if BOB doesn't have any record that has score over 20. It won't return any result at all. May be I should have stressed that point. In this case, the result should be like :

1,BOB,0 //Saying BOB has 0 records that has score over 20.

So I'm clear with my question now?

He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus

[Eytukan]

Something like ?

select * from
(select userid,username from temptable where userid=1 group by userid,username)as a
inner join
(select userid, count as cnt from temptable where score>21
group by userid)as b on a.userid=b.userid

But this doesn't work too :sigh:

He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus

[Eytukan]

Got it almost!

select * from
(select userid,username from temptable where userid=1
group by userid,username)as a
left join (select userid, count(*)as cnt from temptable
where score>21 group by userid)as b on a.userid=b.userid

But this returns "NULL" instead of 0. How can I make it 0 ? Can I use "CASE" on the count? Like:

CASE cnt when NULL then 0 END

He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus

[ed5756]

OK, you want to return a 0 if there are no scores over 20. That is trickier. This will return the count of Bob's scores over 20:

Select userid,username,count(*) as [count] from temptable where userid=1 and score>20 group by userid

This union query will return that same result and also a dummy record with a 0 count:

Select userid,username,count(*) as ct from temptable as a where userid=1 and score>20 group by userid, username
union
Select top 1 userid,username,0 as ct from temptable as b where userid=1

One would think then that using the MAX function in a sub-select would get your desired result:

select userid,username,max(ct) as
from
(Select userid,username,count(*) as ct from temptable as a where userid=1 and score>20 group by userid, username
union
Select top 1 userid,username,0 as ct from temptable as b where userid=1)
group by userid,username

But I find that in T-SQL for some reason it throws an error. However, if I first define a view based on that union, then this works:

select userid,username,max(ct) from view_c as Over20 group by userid, username

[Eytukan]

looks big :rolleyes: It will take a day for at least to understand. So you can be free till I come back :). Thanks for your reply. Also you may have a look at my attempts that I posted in my last thread. Thanks :thumbsup:

He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus

[Jorgen Andersson]

How about:

SELECT DISTINCT userid
,username
,(SELECT Count(*) FROM temptable t2 WHERE t1.userid=t2.userid AND score > 20) as topscorecount
FROM temptable t1
WHERE userid = 1

"Lots of programmers have had sex - some have even had it with members of the same species." - Pete O'Hanlon

[ed5756]

Sorry, there was a typo in my last post. That query should have read

select userid,username,max(ct) as Over20
from
(Select userid,username,count(*) as ct from temptable as a where userid=1 and score>20 group by userid, username union Select top 1 userid,username,0 as ct from temptable as b where userid=1) group by userid,username

I have run the 2-step solution using a view on SQL Server and it works.

[Eytukan]

Yeah thanks. Also the one provided by Jorgen is also cool.

He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus

[Eytukan]

:thumbsup:

He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus

[anup keshari]

select count(*),userid,username from template where username ='Bob'and score > 20 group by userid,username

[Eytukan]

Check here[^]

He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus

[anup keshari]

select count(*),userid,username from temptable where username ='Bob' and score > 20 group by userid,username union select 0,userid,username from temptable where username ='Bob'and score < 20 group by userid,username

[i j russell]

set nocount on create table tempuser (userid int, username varchar(10)) create table tempscore(userid int, score int) insert into tempuser (userid, username) values (1, 'Bob') insert into tempscore (userid, score) values(1, 17) insert into tempscore (userid, score) values(1, 21) select u.userid, u.username, coalesce(s.total, 0) as total from tempuser u left outer join ( select userid, count(*) as total from tempscore where score > 20 group by userid ) s on s.userid = u.userid drop table tempscore drop table tempuser