calculate between 2 columns - 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

General SQL Server Forums

New to SQL Server Programming

calculate between 2 columns

Author

Topic

juliactm
Starting Member

2 Posts

Posted - 2012-10-18 : 06:08:23

I need to get records that have difference in counts(15000617,15000789), illuminating records with the same count(15001968). How can I combine these two queries and do the this selection?

select client, count(*)as all from clients group by client
select client, count(*) as with_condition from clients where reason='AUTO' group by client

Result:

CLIENT ALL CLIENT WITH_CONDITION
15000617 4 15000617 2
15000789 5 15000789 1
15001968 5 15001968 5

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-10-18 : 06:19:39

select client, all, with_condition, case when all=with_condition then 'illuminated' else null end as illumination
from
(
select
client,
count(*) as all,
sum(case when reason='AUTO' then 1 else 0 end) as with_condition
from clients group by client
)dt

Too old to Rock'n'Roll too young to die.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-18 : 06:22:41

Hi,


SELECT distinct client
		, COUNT(*) over() 'Count All'
		,case when reason = 'AUTO' Then COUNT(*) over(partition by client) 
				else 0 
		 end as CountWithCondition
FROM clients

--
Chandu

juliactm
Starting Member

2 Posts

Posted - 2012-10-18 : 09:14:31

thanks guys - it works!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources