So here is the code which is working fine but not grouping the duplicated values.

SELECT
    CONCAT(driver.first_name, ' ', driver.last_name) AS [Driver Name],
    AVG(RATING.score) AS Average_Score,
    driver.driver_id,
    CASE 
        WHEN AVG(RATING.score) = 1 THEN 'Driver is very bad.'
        WHEN rating.score = 2 THEN 'Driver is bad.'
        WHEN RATING.score = 3 THEN 'Driver is okay.'
        WHEN RATING.score = 4 THEN 'Driver is good.'
        WHEN RATING.score = 5 THEN 'Driver is excellent.'
    END AS [Driver Performance]
FROM
    CALL
INNER JOIN 
    RATING ON CALL.rating_id = RATING.rating_id
INNER JOIN 
    DRIVER ON CALL.driver_id = DRIVER.driver_id
GROUP BY   
    driver.first_name, driver.last_name, driver.driver_id, rating.score

As you can see mehmet cinar should had 3 average_score but I couldn't do it.

CodePudding user response：

First you should use avg(RATING.score) across all the CASE options consistently. Next, the aggregated column shouldn't be on the GROUP BY list.

select  CONCAT(driver.first_name,' ',driver.last_name)as [Driver Name]
, avg(RATING.score) as Average_Score,driver.driver_id,
CASE WHEN avg(RATING.score) = 1 THEN 'Driver is very bad.'
  WHEN avg(RATING.score) = 2 THEN 'Driver is bad.'
  WHEN avg(RATING.score) = 3 THEN 'Driver is okay.'
  WHEN avg(RATING.score) = 4 THEN 'Driver is good.'
  WHEN avg(RATING.score) = 5 THEN 'Driver is excellent.'
END AS [Driver Performance]
FROM CALL
INNER JOIN RATING ON CALL.rating_id=RATING.rating_id
INNER JOIN DRIVER ON CALL.driver_id=DRIVER.driver_id
GROUP BY driver.first_name,driver.last_name,driver.driver_id

CodePudding user response：

The following sample code shows how to correctly group by driver and compute the average score

create table driver (id int identity primary key, fullname nvarchar(450))
create table rating (id int identity primary key, driverId int, score float)
insert driver values ('Fabio'), ('Laura')
insert rating values (1, 2), (1, 3), (2, 4), (2, 5)
select driver.fullname FullName, avg(rating.score) Score
    from driver join rating on driver.id = rating.driverId group by driver.id, driver.fullname

Result

Fabio