Except Does not seem to work - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Except Does not seem to work

Author

Topic

tmurphy686
Starting Member

2 Posts

Posted - 2013-12-17 : 09:20:18

Below is my query where the "Except" portion does not seem to work. I get a result of 181 by running this and get a result of 181 by running the upper query - any help you can provide would be greatly appreciated:

select s_orig_br, count(s_acct_nbr)
from dbo.savings
where s_opening_dt >= '1/01/2013'
and s_opening_dt <= '1/31/2013'
and s_orig_br <> '500'
group by s_orig_br
except
select s_orig_br, count(s_acct_nbr)
from dbo.savings
where s_acct_type = '1'
and s_appl_code = '00'
and s_opening_dt >= '1/01/2013'
and s_opening_dt <= '1/31/2013'
and s_orig_br <> '500'
group by s_orig_br
order by 1;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-17 : 09:40:16

do you mean this?


select s_orig_br, count(s_acct_nbr)
from dbo.savings s
where s_opening_dt >= '1/01/2013'
and s_opening_dt <= '1/31/2013'
and s_orig_br <> '500'
and where not exists (select 1
from dbo.savings
where s_acct_type = '1'
and s_appl_code = '00'
and s_opening_dt >= '1/01/2013'
and s_opening_dt <= '1/31/2013'
and s_orig_br <> '500'
and s_orig_br = s.s_orig_br)
group by s_orig_br
order by 1;

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

tmurphy686
Starting Member

2 Posts

Posted - 2013-12-17 : 09:48:07

That does not seem to run. I get a "where" issue on line 6 and a "group " issue on line 14. Shouldn't except have worked though?

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2013-12-24 : 13:11:21

In your first query you are returning all of the results - in the second query you are returning only those results where s_acct_type = 1 and s_appl_code = '00'. What your except statement is stating is to return any rows from the first statement that do not exist in the second statement.

In other words, exclude from the first result any rows where s_orig_br and count(s_acct_nbr) are equal to the totals from the second result.

I don't see how this could work, as you would never find a match for the total count where the first set includes s_acct_type = 1 and s_appl_code = '00' and the second result only totals on those specific values.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-25 : 00:57:32

quote:
Originally posted by tmurphy686

That does not seem to run. I get a "where" issue on line 6 and a "group " issue on line 14. Shouldn't except have worked though?

that was a typo


select s_orig_br, count(s_acct_nbr)
from dbo.savings s
where s_opening_dt >= '1/01/2013'
and s_opening_dt <= '1/31/2013'
and s_orig_br <> '500'
and not exists (select 1
from dbo.savings
where s_acct_type = '1'
and s_appl_code = '00'
and s_opening_dt >= '1/01/2013'
and s_opening_dt <= '1/31/2013'
and s_orig_br <> '500'
and s_orig_br = s.s_orig_br)
group by s_orig_br
order by 1;

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources