Search code examples
sqlsumdistincthaving

Select distinct values where condition having sum < value

I have a problem with a SQL query. I need to get a list of all unique [Cod Angajat] field for which the sum of all entries in the column [Timp declarat] is less than 8, with the where clause. Bellow is what I've gotten so far.

In the table I have 3 rows. It returns me the same rows, instead of 1 row, for which all the conditions apply - meaning the one with [Cod Angajat] = 2.

|   Cod Angajat  |   Nume   |  Timp declarat  |
---------------------
|  1             |  Ene     | 3               |
|  1             |  Ene     | 5               |
|  2             |  Gigi    | 4               |

select COUNT(DISTINCT [Cod Angajat]), [Cod Angajat], [Nume], [Timp declarat]
FROM [SC Vermorel SRL$ProductieVE]
WHERE (sum([Timp declarat] < 8 AND cast(CONVERT(varchar(8), Data, 112) As DateTime) = @data2) and ([Schimb] = '" & ProceseazaSCH(Now()) & "' 
GROUP BY [Nume], [Cod Angajat], [Timp declarat]
HAVING sum([Timp declarat]) < 8
Solution

  • If you need one row per [Cod Angajat], then that should be in the GROUP BY -- and nothing else:

    SELECT [Cod Angajat], SUM([Timp declarat]
FROM [SC Vermorel SRL$ProductieVE]
WHERE CONVERT(date, Data) = @data2 AND
      ([Schimb] = '" & ProceseazaSCH(Now()) & "'
GROUP BY [Cod Angajat]
HAVING SUM([Timp declarat]) < 8;

    The rest of the query simplifies as well. There is no need to convert a date/time to a string to remove the time component. And aggregation functions don't belong in the WHERE clause.