sql noob question on COUNT

Eytukan

A simple table:

create table temptable(userid int,username varchar(100),score int)

Values inserted:

insert into temptable values(1,'BOB',17)
insert into temptable values(1,'BOB',21)

Test:

select * from temptable where userid =1

Result:

1,BOB,17
1,BOB,21
Returns 2 rows. Fine.

The requirement: Find number of times BOB has scored above 20. Expected result

1,BOB,1 //Where the last "1" says that he's got 1 score above 20 (the count)

My attempt:

select userid,username,count(*) from temptable where userid=1 group by userid,username

Here how do I specify a condition for the "Count()"? It just need to count score>20 for BOB. I'm a rock bottom sql noob. Any help?

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

Try:

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

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