ORDER BY与group BY（hsqldb）不能正确工作

Question

CREATE TABLE DEPARTMENT
(
    ID       INTEGER PRIMARY KEY,
    NAME     VARCHAR(14),
    LOCATION VARCHAR(13)
);

CREATE TABLE EMPLOYEE
(
    ID         INTEGER PRIMARY KEY,
    FIRSTNAME  VARCHAR(10),
    LASTNAME   VARCHAR(10),
    MIDDLENAME VARCHAR(10),
    POSITION   VARCHAR(9),
    MANAGER    INTEGER,
    HIREDATE   DATE,
    SALARY     DOUBLE,
    DEPARTMENT INTEGER,
    CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPARTMENT) REFERENCES DEPARTMENT (ID)
);

SELECT name AS depname, SUM(salary) AS TOTAL, MEDIAN(salary) AS AVERAGE 
FROM EMPLOYEE 
  JOIN DEPARTMENT ON department.id = employee.department
GROUP BY (PUBLIC.DEPARTMENT.NAME) 
ORDER BY TOTAL DESC

Answer 1

该查询在逻辑上看起来是正确的。我可以建议尝试重写它使用适当的别名到处。考虑使用此版本：

sql prettyprint-override">SELECT
    d.name AS depname,
    SUM(e.salary) AS TOTAL,
    MEDIAN(e.salary) AS AVERAGE
FROM EMPLOYEE e
INNER JOIN DEPARTMENT d
    ON d.id = e.department
GROUP BY
    d.name
ORDER BY
    TOTAL DESC;