Summation of distinct rows - SQL Server I am pretty sure I am making a basic mistake, but I can't figure it out for the life of me.  I am trying to get a simple report of total number of unique employees, number of employees who are male, number of employees who are female, and finally the total gross pay for the entire month

My Query:

SELECT COUNT(DISTINCT prg.PrgEEID) AS 'Emp Total'
  , SUM(CASE WHEN eep.EepGender='M' THEN 1 ELSE 0 END)
  , SUM(prg.prgsuigross) as '2017 Gross'
  , MONTH(prg.prgpaydate) AS '2017 Month'
  FROM PayReg prg
JOIN EmpPers eep
  ON prg.prgeeid=eep.eepEEID
WHERE prg.PrgStateSui like 'HI%'
  AND YEAR(prg.prgpaydate)='2017'
  AND prg.PrgCheckAddMode in ('A','R','M')
GROUP BY MONTH(prg.prgpaydate)
ORDER BY MONTH(prg.prgpaydate)


Results:

33  58  92131.95    1
32  77  127300.16   2
32  88  159734.80   3
32  68  121667.13   4
32  61  96918.07    5
32  71  125784.19   6
33  82  92163.50    7
33  88  114839.29   8
32  92  134167.71   9
30  64  109269.71   10
30  61  93108.24    11
32  77  96319.64    12


I know the male total is 24 for month one, but I am getting 58 .. and that is because i am getting each transaction for the month and not the unique number of males.  I tried using an OVER PARTITION BY Clause, and that only made it worse.
COUNT() ignores NULL values, and you can use DISTINCT in COUNT() as well.

Try:

  , COUNT(DISTINCT CASE WHEN eep.EepGender='M' THEN eep.eepEEID ELSE NULL END)


(assuming that eep.eepEEID is the unique ID for an employee).

You're doing a unique count of the employee's ID (for males) and NULL (for not males); the NULLs are ignored, and the DISTINCT means that each unique ID will only be counted once.