ORDER BY with group by (hsqldb) don't work correctly-CodePudding

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

enter image description here

CodePudding user response：

The query looks logically correct. I can suggest try to rewrite it using proper aliases everywhere. Consider using this version:

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;

CodePudding user response：

What error message do you get?

Is this all of the code? Because you don't have any values in the tables so there is no way to calculate mean and median.

Also, shouldn't the GROUP BY clause read "DEPARTMENT.NAME" instead of "PUBLIC.DEPARTMENT.NAME"?

CodePudding user response：

try this: ... GROUP BY d.name order by sum(e.salary)