Author |
Topic  |
|
Blue_2509
Starting Member
8 Posts |
Posted - 10/24/2013 : 09:57:44
|
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
3873 Posts |
Posted - 10/24/2013 : 10:12:55
|
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 |
 |
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 10/24/2013 : 10:43:18
|
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
|
 |
|
Blue_2509
Starting Member
8 Posts |
Posted - 10/25/2013 : 02:23:11
|
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! |
 |
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 10/25/2013 : 02:28:23
|
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 |
 |
|
Blue_2509
Starting Member
8 Posts |
Posted - 10/25/2013 : 07:57:37
|
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.
|
 |
|
James K
Flowing Fount of Yak Knowledge
3873 Posts |
Posted - 10/25/2013 : 08:25:32
|
Can you post a sample data set that demonstrates the problem? |
 |
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 10/25/2013 : 08:30:35
|
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
|
 |
|
Blue_2509
Starting Member
8 Posts |
Posted - 10/28/2013 : 07:14:00
|
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. |
 |
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 10/28/2013 : 07:54:23
|
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
|
 |
|
Blue_2509
Starting Member
8 Posts |
Posted - 10/28/2013 : 08:39:19
|
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 |
 |
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 10/28/2013 : 09:09:53
|
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
|
 |
|
Blue_2509
Starting Member
8 Posts |
Posted - 10/29/2013 : 03:07:32
|
Thanks for help! It seems to work now :) |
 |
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
|
waterduck
Aged Yak Warrior
Malaysia
982 Posts |
Posted - 10/29/2013 : 22:17:00
|
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 |
 |
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 10/30/2013 : 01:07:20
|
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
|
 |
|
waterduck
Aged Yak Warrior
Malaysia
982 Posts |
Posted - 10/30/2013 : 02:43:45
|
noted and thx! |
 |
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
|
|
Topic  |
|