Question re: Group by and Having clauses

Posted on 2011-10-17
Last Modified: 2012-05-12
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


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.

Question by:c4e41961
    LVL 8

    Accepted Solution

    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
    LVL 13

    Assisted Solution

    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


    Select count(employee) as total
    From test
    Group by employee
    Having sum(costs) > 0

    Author Closing Comment

    Thank you very much!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now