hey folks,
I am trying to run a query on sql server using the query analyzer ... the query works just fine, but I am attempting to IGNORE any instance where a unique identifier has a sum of units less than zero, and the query doesn't seem to do that:
select provider, fiscal_year, count(distinct ID), sum(finalnetamt), sum(new_quantity) from claimsunion where (stat = 'paid') group by provider, fiscal_year having order by provider, fiscal_year
The above correctly renders to me the proper counts and sum of units and dollars by provider and fiscal year.
Some of the IDs have a 0 sum of (new_quantity) and those I am attempting to ignore from the count, because they wash out/net 0, and should not be counted ... so in an attempt to do that, I am trying a HAVING clause ...
HAVING sum(new_quantity) >0
which renders the same answer and does not remove the IDs that have 0 from the count. I think it is because I don't know how to code that piece that tells sql that it is a having sum(new_quantity) >0 by ID within the group, if that makes sense?
Any help would be greatly appreciated. I know that if I add ID to the group and then use the HAVING sum(new_quantity) >0 it works but it gives me every single ID number and I just want the count not every single ID number.
Thank you very much!!!
I am trying to run a query on sql server using the query analyzer ... the query works just fine, but I am attempting to IGNORE any instance where a unique identifier has a sum of units less than zero, and the query doesn't seem to do that:
select provider, fiscal_year, count(distinct ID), sum(finalnetamt), sum(new_quantity) from claimsunion where (stat = 'paid') group by provider, fiscal_year having order by provider, fiscal_year
The above correctly renders to me the proper counts and sum of units and dollars by provider and fiscal year.
Some of the IDs have a 0 sum of (new_quantity) and those I am attempting to ignore from the count, because they wash out/net 0, and should not be counted ... so in an attempt to do that, I am trying a HAVING clause ...
HAVING sum(new_quantity) >0
which renders the same answer and does not remove the IDs that have 0 from the count. I think it is because I don't know how to code that piece that tells sql that it is a having sum(new_quantity) >0 by ID within the group, if that makes sense?
Any help would be greatly appreciated. I know that if I add ID to the group and then use the HAVING sum(new_quantity) >0 it works but it gives me every single ID number and I just want the count not every single ID number.
Thank you very much!!!
