SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 (Resolved)Subtract to count columns?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mattech13
Starting Member

4 Posts

Posted - 09/08/2013 :  09:04:15  Show Profile  Reply with Quote
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,





Edited by - mattech13 on 09/15/2013 14:14:43

bitsmed
Constraint Violating Yak Guru

433 Posts

Posted - 09/08/2013 :  10:47:33  Show Profile  Reply with Quote
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
Go to Top of Page

mattech13
Starting Member

4 Posts

Posted - 09/08/2013 :  11:21:45  Show Profile  Reply with Quote
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,
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

433 Posts

Posted - 09/08/2013 :  11:30:33  Show Profile  Reply with Quote
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,

Go to Top of Page

mattech13
Starting Member

4 Posts

Posted - 09/15/2013 :  07:55:37  Show Profile  Reply with Quote
yep that's great, thanks for that. How do I mark this as closed?
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

433 Posts

Posted - 09/15/2013 :  11:12:25  Show Profile  Reply with Quote
There's really no way to close this thread, but to change subject to:

(Resolved) Date comparison problem
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000