SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to exclude values in sql ??
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Blue_2509
Starting Member

8 Posts

Posted - 10/24/2013 :  09:57:44  Show Profile  Reply with Quote
Hi,
please help me with exclude values when Account have different values (rooms both A and B), i.e

Account Room
10122 A
10122 B
10130 B
10131 C

Thank you advanced! Blue

James K
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 10/24/2013 :  10:12:55  Show Profile  Reply with Quote
Either of these.
SELECT Account, room 
FROM Tbl
GROUP BY Account
HAVING COUNT(*) = 1

SELECT Account, Room FROM (
SELECT Account, Room,
	COUNT(*) OVER (PARTITION BY account) AS N
FROM Tbl
) s WHERE N=1
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/24/2013 :  10:43:18  Show Profile  Reply with Quote
which values you want to exclude?
Entire Account records or just duplicate instances?
What would be output for above sample data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Blue_2509
Starting Member

8 Posts

Posted - 10/25/2013 :  02:23:11  Show Profile  Reply with Quote
Thank you James, I will try this.

@visakh16: I need to include only those accounts where have 1 room value. The results shoulb be:
Account Room
10130 B
10131 C

Thanks you for answer!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/25/2013 :  02:28:23  Show Profile  Reply with Quote
then previous suggestion would work for you

or even this


SELECT Account, Room
FROM Tbl t
WHERE NOT EXISTS(SELECT 1
                 FROM Tbl
                 WHERE Account = t.Account
                 AND Room <> t.Room
                 )


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 10/25/2013 02:28:48
Go to Top of Page

Blue_2509
Starting Member

8 Posts

Posted - 10/25/2013 :  07:57:37  Show Profile  Reply with Quote
Thank you, but ... I didn´t get result I wish :S
There were still included Accounts what have rooms A and rooms B.
I have tried all 3 examples.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 10/25/2013 :  08:25:32  Show Profile  Reply with Quote
Can you post a sample data set that demonstrates the problem?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/25/2013 :  08:30:35  Show Profile  Reply with Quote
quote:
Originally posted by Blue_2509

Thank you, but ... I didn´t get result I wish :S
There were still included Accounts what have rooms A and rooms B.
I have tried all 3 examples.



Then i'm sure you've some other rules which you've not told us so far
Post some sample data to show your exact scenario and then explain what you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Blue_2509
Starting Member

8 Posts

Posted - 10/28/2013 :  07:14:00  Show Profile  Reply with Quote
select
account,
Room,
sum(amounts) as booked
from table1
join ...
where --
group by
account,
Room
having count(accont) =1

And results come:

Account,Room,booked
53925,'E 4.000000
54834,'L 1.000000
60817,'A 0.000000
60816,'A 1.000000
60816,'D 1.000000
60816,'C 2.000000
62698,'B 8.000000
62798,'D 2.000000

I need that 60816 will not comes to report results.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/28/2013 :  07:54:23  Show Profile  Reply with Quote

SELECT Account, Room,amounts
FROM Tbl t
WHERE NOT EXISTS(SELECT 1
                 FROM Tbl
                 WHERE Account = t.Account
                 AND Room <> t.Room
                 )


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Blue_2509
Starting Member

8 Posts

Posted - 10/28/2013 :  08:39:19  Show Profile  Reply with Quote
I´m very grateful for answer. But its bit confusing me. Do you mean query like this?

select
t1.account,
t1.room,
t1.booked

from (

select
account,
room,
sum(booked)
from Table1
join ...
where ...
group by
account,
room)t1

where not exists (select 1 from t1 where t1.account =account and room <>t1.room

group by

select
t1.account,
t1.room,
t1.booked

Edited by - Blue_2509 on 10/28/2013 08:39:55
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/28/2013 :  09:09:53  Show Profile  Reply with Quote
nope just this


select
account,
room,
booked
from Table1 t1
join ... 
where ... 
and not exists (select 1 from Table1 
                where account = t1.account 
                and room <>t1.room)




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Blue_2509
Starting Member

8 Posts

Posted - 10/29/2013 :  03:07:32  Show Profile  Reply with Quote
Thanks for help! It seems to work now :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/29/2013 :  03:16:26  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 10/29/2013 :  22:17:00  Show Profile  Reply with Quote
visakh, sorry to bother...can i know why using "not exist" but not using "having count(*) = 1"?
just for my better understanding, thanks alot ya
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/30/2013 :  01:07:20  Show Profile  Reply with Quote
quote:
Originally posted by waterduck

visakh, sorry to bother...can i know why using "not exist" but not using "having count(*) = 1"?
just for my better understanding, thanks alot ya


yon can use both ways as they're equivalent
But NOT EXISTS might have slight upper hand as it relies on boolean result rather than performing aggregation to find count

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 10/30/2013 :  02:43:45  Show Profile  Reply with Quote
noted and thx!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/30/2013 :  03:23:40  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.3 seconds. Powered By: Snitz Forums 2000