• Status: Solved
• Priority: Medium
• Security: Public
• Views: 226

# 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
2 Solutions

Commented:
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

Commented:
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

Author Commented:
Thank you very much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.