sql noob question on COUNT
-
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
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=1One 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,usernameBut 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
-
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=1One 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,usernameBut 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
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
-
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>20for 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
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
-
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
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,usernameI have run the 2-step solution using a view on SQL Server and it works.
-
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,usernameI have run the 2-step solution using a view on SQL Server and it works.
-
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
-
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>20for 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
select count(*),userid,username from template where username ='Bob'and score > 20 group by userid,username
-
select count(*),userid,username from template where username ='Bob'and score > 20 group by userid,username
-
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
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
-
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>20for 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
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