sql count function - 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

sql count function

Author

Topic

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2012-02-03 : 15:12:24

I am trying to count the number of positive and negative values using a group by and count. Here is my code

select distinct code,
count (code),
count (bill_amt),
count (allow_amt)
from mytable
group by code, bill_amt, allow_amt;

Here is an example of the table but there are really 16 million rows. I want to take all the codes where there are 7 and count the codes and then the positive amts and negative amts per each.

code billamt allowamt
a 20.00 15.00
a -15.00 15.00
b 7.00 -12.00
c 4.00 -17.00
d 37.00 -30.00

The goal is group by and count all 16 million and put it into negative or positive counts. So the above example my result I want to look like this

code countof countof countof countof countof
code negba posba negaa posaa
a 4 1 1 2
b 2 1 1
c 2 1 1
d 2 1 1

I know how many counts of code I have for the 16mil but want to see how many of those overall are pos and how many neg

tina m miller

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-03 : 15:43:12

I don't understand your expected output for the example you gave. Are you sure it's right? The second column seems to be the count for all amounts, but you've got it labeled as negba. I'm confused.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-03 : 15:46:08

Maybe you lost formatting when you posted your expected output. You have to put code tags around it to retain spacing.

How about this:


declare @t table (code char(1), billamt int, allowamt int)

insert into @t values('a', 20, 15)
insert into @t values('a', -15, 15)
insert into @t values('b', 7, -12)
insert into @t values('c', 4, -17)
insert into @t values('d', 37, -30)

select code, 
	countof = count(billamt) + count(allowamt),
	negba = sum(case when billamt < 0 then 1 else 0 end),
	posba = sum(case when billamt > 0 then 1 else 0 end),
	negaa = sum(case when allowamt < 0 then 1 else 0 end),
	posaa = sum(case when allowamt > 0 then 1 else 0 end)
from @t
group by code

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2012-02-03 : 16:14:34

Let me try this again. I have a table that I cannot change. It is on the company's server. It has 16 million records. I can get a group by and count of the code but now they want to know of those counts how many are positive bill amount and negative and then same for allow amount. Now I can break it down I think by saying group by code, count by code and then a group by bill amount where it is a positive and then count the number of positives and then just used mathematics to determine the number of negatives. Then do a separate query for the allow amount. But putting values with insert into I cannot do as I only have read writes to the data.

tina m miller

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-03 : 16:55:27

Did you try my query? I didn't say to change anything about the table. The declare/insert into are not for you to use. They are there to show my test. Notice how your data is being inserted into my test table. It's only a test table. Now take my select query and change the table name and see if it works for you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources