|
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
|
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
Dear friend
I want to write a query that produced single row find the max form two table and also
query that delete all redundant data from table in sql 2005
Thanks and regards
Azad Yadav
|
|
|
|
|
azad yadav wrote: write a query that produced single row find the max form two table
you can get max for each table and then find max from two result . same as this code:
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
This is a general question , you must specify what you mean exactly.
|
|
|
|
|
one thing between two table should have delete cascading on master table .
then u can use
delete from mastertable where id = select top 1 id from (select max(id) as id from master a join detail b on a.id=b.id )as a
|
|
|
|
|
I created a table in scott schema and stored some unicode charaters through iSQL*PLus in internet explorer , I can check the data in this invironment correctly while I am checking that through Oracle SQL*Plus it is displaying as ?????? when I installed the oracle I select the required language package as well (Arabic), I can see my data only Interent explore (iSQL*Plus), I can not see the data in Forms , or in Reports
|
|
|
|
|
If you use unicode characters I recommend using nvarchar2 instead of varchar2, then you won't have conversion problems.
It still won't work in SQL plus though as it does not support unicode characters as far as I know.
"Lots of programmers have had sex - some have even had it with members of the same species." - Pete O'Hanlon
|
|
|
|
|
Is there anyway that we can extract or backup data from a sql server table to anything, and later importing them?
Is there any popular tool or technique to do so?
|
|
|
|
|
you can generate sql script for you table structure and also data which exist in your table.
if you have sql server 2008 follow this instructions:
Open Sql Server Management Studio
Right Click on your database
go to : Tasks
then: generate scripts...
wizard will shown then
try this wizard you can find it very simple
|
|
|
|
|
I have to do that programatically and i am using Vb.Net or may be C# will do.
|
|
|
|
|
You can create your own SSIS package and then run it with your .NET application
|
|
|
|
|
Hello ,
You can use SMO ( SQL Server Management Objects ) to do that but it might be complicated for a beginner ( in SMO )
|
|
|
|
|
Dear Friends,
I attached sql 2000 database in sql 2005 server and deattaching from 2005 again try to attached with sql 2000 then give error (could not find sysindexes for database id 9 Run dbcc chack table on sysindexes)
Please help me
Thanks and Regards
Azad Yadav
|
|
|
|
|
I will be astonished if you can do this. The idea that you can take a 05 db back to 2000 seems unlikely in the extreme.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
use Integration Service and transfer your database from 2005 to 2000.because you can not restore 2005 on 2000 on usual ways
|
|
|
|