'SQL Server: GROUP BY with multiple columns produces duplicate results

I'm trying to include a 3rd column into my existing SQL Server query but I am getting duplicate result values.

Here is an example of the data contained in tb_IssuedPermits:

| EmployeeName | Current |
|--------------|---------|
| Person A     | 0       |
| Person A     | 0       |
| Person B     | 1       |
| Person C     | 0       |
| Person B     | 0       |
| Person A     | 1       |

This is my current query which produces duplicate values based on 1 or 0 bit values.

SELECT EmployeeName, COUNT(*) AS Count, [Current]
FROM tb_IssuedPermits
GROUP BY EmployeeName, [Current]

| EmployeeName | Count | Current |
|--------------|-------|---------|
| Person A     | 2     | 0       |
| Person B     | 1     | 0       |
| Person C     | 1     | 0       |
| Person A     | 1     | 1       |
| Person B     | 1     | 1       |

Any ideas on how I can amend my query to have the following expected result? I want one result row per EmployeeName. And Current shall be 1, if for the EmployeeName exists a row with Current = 1, else it shall be 0.

| EmployeeName | Count | Current |
|--------------|-------|---------|
| Person A     | 3     | 1       |
| Person B     | 2     | 1       |
| Person C     | 1     | 0       |

The result does not need to be in any specific order.

TIA

sqlsql-server


Solution 1:[1]

If your Current column contains the string values 'FALSE' and 'TRUE' you can do this

SELECT EmployeeName, Count(*) AS Count, 
       MAX([Current]) AS Current
  FROM tb_IssuedPermits
 GROUP BY EmployeeName

It's a hack but it works: MAX will get the TRUE from each group if there is one.

If your Current column is a BIT, cast to INT and cast back, as @ThorstenKettner suggested.

SELECT EmployeeName,
       Count(*) AS Count, 
       CAST(MAX(CAST([Current] AS INT)) AS BIT) AS Current
  FROM tb_IssuedPermits
 GROUP BY EmployeeName

Alternatively, you can use conditional aggregation:

SELECT EmployeeName,
       Count(*) AS Count, 
       CAST(COUNT(NULLIF(Current, 0)) AS BIT) AS Current
  FROM tb_IssuedPermits
 GROUP BY EmployeeName

Solution 2:[2]

you can do like this

SELECT EmployeeName, Count(1) AS Count,SUM(CAST([Current]AS INT)) AS Current FROM tb_IssuedPermits GROUP BY EmployeeName

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Suraj S.

Related Questions