SQL, what aggregation logic makes different results?-CodePudding

SQL1 returns lines with aggreated names while SQL2 returns the non-aggreated.

Question is what's the difference of aggregation logic when executing the two SQLs. Thanks.

SQL1

SELECT
    name,
    CASE WHEN COUNT(CASE WHEN course = 'SQL' THEN 1 END) > 0  THEN 'o' END AS SQL,
    CASE WHEN COUNT(CASE WHEN course = 'UNIX' THEN 1 END) > 0 THEN 'o' END AS UNIX,
    CASE WHEN COUNT(CASE WHEN course = 'Java' THEN 1 END) > 0 THEN 'o' END AS Java
FROM Courses
GROUP BY name;

SQL2

SELECT  name,
       CASE WHEN course = 'SQL' THEN '○' ELSE NULL END s,
       CASE WHEN course = 'UNIX' THEN '○' ELSE NULL END u,
       CASE WHEN course = 'Java' THEN '○' ELSE NULL END j
FROM Courses
GROUP BY name,course;

Create Table

CREATE TABLE Courses
(name   VARCHAR(32), 
 course VARCHAR(32), 
 PRIMARY KEY(name, course));

INSERT INTO Courses VALUES('Tom', 'SQL');
INSERT INTO Courses VALUES('Tom', 'UNIX');
INSERT INTO Courses VALUES('Jack', 'SQL');
INSERT INTO Courses VALUES('Mike', 'SQL');
INSERT INTO Courses VALUES('Mike', 'Java');
INSERT INTO Courses VALUES('Jane', 'UNIX');
INSERT INTO Courses VALUES('Mary', 'SQL');

CodePudding user response：

I would say that difference in logic is obvious, in first query you group by just name.

GROUP BY name

Basically, you are saying group all rows with same name as one row.

In second query you group by name and course.

GROUP BY name,course

Which means, all rows with same name and same course should be one row.