CODEWITHSUNDEEP
sqlsql-servergroup-by
Michael Wood
Michael Wood

Reputation: 109

In Microsoft SQL How do I return only 1 row per ID with multiple column results

I need to have one row per driver id that has columns that are calculated. Currently the GROUP BY is requiring "JobEndComments" which is creating multiple rows, instead of one.

I am getting:

HOT_DriverID,Driver Name,Successfully Completed,No Post,No Access,Not Allowed,Not Found,Not Tall Enough,No Permission
3   William Arboleda    768 0   0   0   0   0   0<br>
3   William Arboleda    0   0   1   0   0   0   0<br>
4   Fredy Garcia    912 0   0   0   0   0   0<br>
4   Fredy Garcia    0   0   0   0   2   0   0<br>
7   Mario Estrada   497 0   0   0   0   0   0<br>
7   Mario Estrada   0   1   0   0   0   0   0<br>
7   Mario Estrada   0   0   1   0   0   0   0<br>

When I want:

HOT_DriverID,Driver Name,Successfully Completed,No Post,No Access,Not Allowed,Not Found,Not Tall Enough,No Permission
3   William Arboleda    768 0   1   0   0   0   0<br>
4   Fredy Garcia    912 0   0   0   2   0   0<br>
7   Mario Estrada   497 1   1   0   0   0   0<br>

I have tried eliminating the GROUP BY on JobEndComments but this generates errors.

SELECT HOT_DriverID, d.First_Name + ' ' + d.Last_Name AS 'Driver Name', 
ISNULL((SELECT COUNT(JobEndComments) WHERE JobEndComments = '000' AND HOT_DriverID = HOT_DriverID),0) AS 'Successfully Completed',
ISNULL((SELECT COUNT(JobEndComments) WHERE JobEndComments = '111' AND HOT_DriverID = HOT_DriverID),0) AS 'No Post',
ISNULL((SELECT COUNT(JobEndComments) WHERE JobEndComments = '222' AND HOT_DriverID = HOT_DriverID),0) AS 'No Access',
ISNULL((SELECT COUNT(JobEndComments) WHERE JobEndComments = '333' AND HOT_DriverID = HOT_DriverID),0) AS 'Not Allowed',
ISNULL((SELECT COUNT(JobEndComments) WHERE JobEndComments = '444' AND HOT_DriverID = HOT_DriverID),0) AS 'Not Found',
ISNULL((SELECT COUNT(JobEndComments) WHERE JobEndComments = '555' AND HOT_DriverID = HOT_DriverID),0) AS 'Not Tall Enough',
ISNULL((SELECT COUNT(JobEndComments) WHERE JobEndComments = '777' AND HOT_DriverID = HOT_DriverID),0) AS 'No Permission'
FROM tbl_Jobs_IFC j
JOIN Drivers d ON d.Code = j.HOT_DriverID
WHERE ConfirmedDate IS NOT NULL
AND HOT_DriverID > 0
AND (HOT_Date >= @From AND HOT_Date < @To)
AND d.First_Name NOT LIKE 'Pend%'
AND d.First_Name NOT LIKE '%E-D%'
AND d.Active > 0
AND d.Printable > 0
--GROUP BY JobEndComments, c.description, c.JobEndComment, Hot_DriverID, d.First_Name, d.Last_Name
--GROUP BY Hot_DriverID, d.First_Name, d.Last_Name, JobEndComment, JobEndComments
GROUP BY Hot_DriverID, d.First_Name, d.Last_Name, JobEndComments
ORDER BY HOT_DriverID, JobEndComments

I really only want one result per driver id. I can work around it via code and deciphering the multiple rows, but I am fairly certain this can be accomplished in SQL. If I leave out JobEndComments in the GROUP BY I get an error:

'tbl_Jobs_IFC.JobEndComments' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Upvotes: 0

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

This is so weird. You are using subqueries with no FROM clause.

Just use conditional aggregation:

SELECT HOT_DriverID, d.First_Name + ' ' + d.Last_Name AS "Driver Name", 
       SUM(CASE WHEN JobEndComments = '000' THEN 1 ELSE 0 END) AS "Successfully Completed",
       SUM(CASE WHEN JobEndComments = '111' THEN 1 ELSE 0 END) AS "No Post",
       . . .
FROM tbl_Jobs_IFC j JOIN
     Drivers d 
     ON d.Code = j.HOT_DriverID
WHERE ConfirmedDate IS NOT NULL AND
      HOT_DriverID > 0 AND
      (HOT_Date >= @From AND HOT_Date < @To) AND
      d.First_Name NOT LIKE 'Pend%'
      d.First_Name NOT LIKE '%E-D%' AND
      d.Active > 0 AND
      d.Printable > 0
GROUP BY Hot_DriverID, d.First_Name, d.Last_Name
ORDER BY HOT_DriverID;

Note that I also changed the escape character for the column aliases. Only use single quotes for date and string constants.

Upvotes: 1

Related Questions