Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2010
    Posts
    19

    Post Unanswered: SQL Count and Group By not showing expected results

    Hello,
    I have an employee time sheet database, and I am simply trying to find out how many employees were active within a given month.

    This is my query:
    Code:
    SELECT COUNT(ID) as count01, hours.empid, employees.class FROM hours, employees 
    WHERE hours.empid = employees.employeeid AND YEAR(clockout) = '2015' AND MONTH(clockout) = 01
    GROUP BY hours.empid
    Now the results I get are not what I want, here is the first couple rows:
    count01 empid class
    28 1 supervisor
    22 3 production
    etc...

    Now I understand the results, employee #1 clocked in and out 28 times within month 01.. but that's not what I want. By grouping a count, should I not get 1 result; the count of rows while grouped? My results right now show 25 rows, that's the info I want output, 25... 25 employees were actively clocking in and out during month 01, I don't care how many individual times they did so.

    Any advice appreciated!

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    a few different ways to skin an onion. Take your pick(keep in mind all off top of head--untested) from these two.

    Simply change your select clause to
    Code:
    SELECT COUNT(distinct(ID)) as count01, hours.empid, employees.class
    Another way is to only get the rows once, might be faster for a very large company database. As it stands unless you have an index on expression for year and month this will be a tablespace scan of one of the two tables, so speed may not be what you are after here for either version of the query.
    Just as a design item, since you want to be able to query by year and month of a date field, you might consider making them their own fields. you could even add them and define the appropriate default, that way programmers, don't have to change insert processes, just start using them in the select.

    Code:
    SELECT COUNT(ID) as count01
         , employees.empid
         , employees.class
       FROM employees 
    WHERE exists (select 1 from hours
                  where hours.empid = employees.employeeid
                    AND YEAR(hours.clockout) = '2015'
                    AND MONTH(hours.clockout) = 01)
    group by  employees.empid
         , employees.class
    I am sure you will get many other suggestions on how to write the SQL.
    Dave

  3. #3
    Join Date
    Dec 2010
    Posts
    19

    Post

    Quote Originally Posted by dav1mo View Post
    a few different ways to skin an onion. Take your pick(keep in mind all off top of head--untested) from these two.

    Simply change your select clause to
    Code:
    SELECT COUNT(distinct(ID)) as count01, hours.empid, employees.class
    Another way is to only get the rows once, might be faster for a very large company database. As it stands unless you have an index on expression for year and month this will be a tablespace scan of one of the two tables, so speed may not be what you are after here for either version of the query.
    Just as a design item, since you want to be able to query by year and month of a date field, you might consider making them their own fields. you could even add them and define the appropriate default, that way programmers, don't have to change insert processes, just start using them in the select.

    Code:
    SELECT COUNT(ID) as count01
         , employees.empid
         , employees.class
       FROM employees 
    WHERE exists (select 1 from hours
                  where hours.empid = employees.employeeid
                    AND YEAR(hours.clockout) = '2015'
                    AND MONTH(hours.clockout) = 01)
    group by  employees.empid
         , employees.class
    I am sure you will get many other suggestions on how to write the SQL.
    Thanks for your suggestions! Unfortunately, the first option renders the same results. The second option does not function, because it seems you have mixed up the colums, empid is not on the employees table. I fixed this but it gives me a table full of 1's.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •