Author |
Topic |
Blue_2509
Starting Member
8 Posts |
Posted - 2013-10-24 : 09:57:44
|
Hi,please help me with exclude values when Account have different values (rooms both A and B), i.eAccount Room 10122 A10122 B10130 B10131 CThank you advanced! Blue |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-24 : 10:12:55
|
Either of these.SELECT Account, room FROM TblGROUP BY AccountHAVING COUNT(*) = 1SELECT Account, Room FROM (SELECT Account, Room, COUNT(*) OVER (PARTITION BY account) AS NFROM Tbl) s WHERE N=1 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-24 : 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Blue_2509
Starting Member
8 Posts |
Posted - 2013-10-25 : 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 B10131 CThanks you for answer! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-25 : 02:28:23
|
then previous suggestion would work for youor even thisSELECT Account, RoomFROM Tbl tWHERE NOT EXISTS(SELECT 1 FROM Tbl WHERE Account = t.Account AND Room <> t.Room ) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Blue_2509
Starting Member
8 Posts |
Posted - 2013-10-25 : 07:57:37
|
Thank you, but ... I didn´t get result I wish :SThere were still included Accounts what have rooms A and rooms B. I have tried all 3 examples. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-25 : 08:25:32
|
Can you post a sample data set that demonstrates the problem? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-25 : 08:30:35
|
quote: Originally posted by Blue_2509 Thank you, but ... I didn´t get result I wish :SThere 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 farPost some sample data to show your exact scenario and then explain what you want------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Blue_2509
Starting Member
8 Posts |
Posted - 2013-10-28 : 07:14:00
|
selectaccount,Room,sum(amounts) as booked from table1join ... where -- group byaccount,Roomhaving count(accont) =1And results come: Account,Room,booked53925,'E 4.00000054834,'L 1.00000060817,'A 0.00000060816,'A 1.00000060816,'D 1.00000060816,'C 2.00000062698,'B 8.00000062798,'D 2.000000I need that 60816 will not comes to report results. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-28 : 07:54:23
|
[code]SELECT Account, Room,amountsFROM Tbl tWHERE NOT EXISTS(SELECT 1 FROM Tbl WHERE Account = t.Account AND Room <> t.Room )[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Blue_2509
Starting Member
8 Posts |
Posted - 2013-10-28 : 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.bookedfrom (selectaccount,room,sum(booked) from Table1join ... where ... group byaccount,room)t1 where not exists (select 1 from t1 where t1.account =account and room <>t1.roomgroup by select t1.account,t1.room, t1.booked |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-28 : 09:09:53
|
nope just thisselectaccount,room,bookedfrom Table1 t1join ... where ... and not exists (select 1 from Table1 where account = t1.account and room <>t1.room) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Blue_2509
Starting Member
8 Posts |
Posted - 2013-10-29 : 03:07:32
|
Thanks for help! It seems to work now :) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-29 : 03:16:26
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2013-10-29 : 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
52326 Posts |
Posted - 2013-10-30 : 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 equivalentBut NOT EXISTS might have slight upper hand as it relies on boolean result rather than performing aggregation to find count------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2013-10-30 : 02:43:45
|
noted and thx! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-30 : 03:23:40
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|