Database

ed5756 wrote:

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

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

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

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

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 

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

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 

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

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

CASE cnt when NULL then 0 END

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

azad yadav wrote:

write a query that produced single row find the max form two table

select max(twotable.fieldmatch) from 
(
select max(field1) as fieldmatch from table1
UNION
select max(field1) as fieldmatch from table2
)twotable

azad yadav wrote:

query that delete all redundant data from table in sql 2005