(Resolved)Subtract to count columns? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

(Resolved)Subtract to count columns?

Author

Topic

mattech13
Starting Member

4 Posts

Posted - 2013-09-08 : 09:04:15

Hi there,

fairly new to sql. Here's essentially what I'm trying to achieve...

select Doctor, count(monthyear)- count(paidinfull) as Patients
from tableA
where paidinfull = 'YES'
group by doctor

So I have a table like

Doctors Patients
------ --------
Burns 12
White 7

where Doctors is a group by from tableA and Patients is a result of the row count of paidinfull = yes minus the rowcount of monthyear

Any help on the actual sql to get this?

thanks,

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-08 : 10:47:33

I think this might work for you.
This will give you the results per doctor/month:


select doctor
      ,monthyear
      ,count(*)-sum(case when paidinfull='YES' then 1 else 0 end) as Patients
  from tableA
 group by doctor
         ,monthyear

This will give you the results per doctor/total:


select doctor
      ,count(*)-sum(case when paidinfull='YES' then 1 else 0 end) as Patients
  from tableA
 group by doctor

mattech13
Starting Member

4 Posts

Posted - 2013-09-08 : 11:21:45

Hi bitsmed,

I'm not entirely sure this solves my problem.

If in tableA there are 100 rows for doctor White. All rows for doctorWhite have a monthyear field so the rowcount for monthyear would be 100. 20 rows have YES in the paidinfull column, so a row count for that would be 20.

I'm looking to display the result of this as

Doctors Patients
------- --------
White 80

where patients is monthyear row count - paidinfull (where it equals = YES)rowcount

thanks,

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-08 : 11:30:33

Why don't you give it a try?
In your latest scenario, count(*) would become 100, and the sum(...) would become 20. Subtracting those two, and you'll have your result (80).

quote:
Originally posted by mattech13

Hi bitsmed,

I'm not entirely sure this solves my problem.

If in tableA there are 100 rows for doctor White. All rows for doctorWhite have a monthyear field so the rowcount for monthyear would be 100. 20 rows have YES in the paidinfull column, so a row count for that would be 20.

I'm looking to display the result of this as

Doctors Patients
------- --------
White 80

where patients is monthyear row count - paidinfull (where it equals = YES)rowcount

thanks,

mattech13
Starting Member

4 Posts

Posted - 2013-09-15 : 07:55:37

yep that's great, thanks for that. How do I mark this as closed?

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-15 : 11:12:25

There's really no way to close this thread, but to change subject to:

(Resolved) Date comparison problem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources