Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

Question re: Group by and Having clauses

I have a simple (I hope) question.
I have a table with two fields. One is person's id number and the other is an amount of money spent on that person.  There are + and- in the money field and in some cases the sum zeros out.  Here is an example:
employee     cost
1234567890      12.00
1234567890      -12.00
1234567890      238.00
1234567890      -238.00
1234567890      40.00
1234567890      -40.00
1234567890      1200.00
1234567890      -1200.00
1122334455      100.00
2233445566  500.00
In this example there are 3 different employees, One of the employees has multiple records but the sum of his cost = 0.  The other two have sums of cost > 0

I want to get a distinct count of all employees with cost > 0

My query is
Select count(distinct employee)
From test
Having sum(cost) > 0

When I run this query the result is 3 even though one of the employees should be filtered out by the Having clause.  

When I run
Select count(disitnct ext_id)
From test
Group by employee
Having sum(cost) > 0

the result is 2 BUT there is a record returned for each employee

1
1


Is there a query that can I can write to get a distinct count of employees and filter out any employees with a sum cost of 0 and not have multiple lines in the result?

Thank you.

0
c4e41961
Asked:
c4e41961
2 Solutions
 
CrashmanCommented:
I really don't understand when you use Group you don't have duplicate values, why use the 'distinct'?

Select count(ext_id)
From test
Group by employee
Having sum(cost) > 0
0
 
p_nutsCommented:
Normally you would also return the employee.

Select employee, sum(costs)
From test
Group by employee
Having sum(costs) > 0

that way you can see ..

If you want to count the employees

Use

Select count(employee) as total
From test
Group by employee
Having sum(costs) > 0
 
0
 
c4e41961Author Commented:
Thank you very much!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now