SQL group by considering multiple rows as 1

Question

lets say i have the following table (courses)

course-------score 

A---------------4

B---------------6

C---------------3

A---------------2

B---------------4

C---------------4

now if i want to get average, min and max for each course ill use the following

SELECT course, AVG(score),MIN(score),Max(score) FROM [courses]
GROUP BY course

but what if i want to consider both course A and B as A so i should get the following

Course-------Average-------Min-------Max

A---------------4----------2-----------6

C--------------3.5---------3-----------4

Answer 1

Your examples seems to suggest you have an error in your question - I am assuming you mean "If I want to consider A and C as A"

In which case you should be able to do this in your GROUP BY

GROUP BY
    CASE WHEN Course = 'C' THEN 'A' ELSE Course END

If that doesn't work, then you can nest a query:

SELECT
    ModifiedCourses.Course,
    AVG(ModifiedCourses.score),
    MIN(ModifiedCourses.score),
    Max(ModifiedCourses.score) 
FROM (
    SELECT
       CASE WHEN Course = 'C' THEN 'A' ELSE Course END AS Course,
       Score
    FROM
        Courses) AS ModifiedCourses
GROUP BY
    ModifiedCourses.Course