SQL Server: GROUP BY with multiple columns produces duplicate results-CodePudding

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

CodePudding user response：

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, do this, as @TorstenKettner suggested.

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

CodePudding user response：

you can do like this

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