Not a group by expression SQL error

Question

SELECT STAFF.StaffID,
       STAFF.Name,
       BRANCH.Name,
       COUNT(RENTAL.StaffID),
       COUNT(MEMBER_REGISTRATION.StaffID)
FROM   STAFF
       LEFT OUTER JOIN RENTAL
         ON STAFF.StaffID = RENTAL.StaffID
       LEFT OUTER JOIN MEMBER_REGISTRATION
         ON STAFF.StaffID = MEMBER_REGISTRATION.StaffID
       INNER JOIN BRANCH
         ON STAFF.BranchID = BRANCH.BranchID
GROUP  BY STAFF.STaffID;

Not a group by expression? i have a similar query without the joins which works fine.

you need to included all *NonAggregated* columns in the `GROUP BY` clause. eg, `...GROUP BY STAFF.StaffID, STAFF.Name, BRANCH.Name` — John Woo, Dec 15 '13 at 21:04
@今草顿 웃: "you need to" --- that's a weird way. You should `GROUP BY` only the columns you need to. If you need to fetch other columns - there are joins available — zerkms, Dec 15 '13 at 21:05
actually mySQL doesn't require you to group by all columns. What RDBMS is this? But in general I agree you likely need to add branch.name and staff.name to your group by — xQbert, Dec 15 '13 at 21:07
@zerkms in Mysql, you can. But in other RDBMS, you can't. Unless I'm missing something. — John Woo, Dec 15 '13 at 21:11
@今草顿 웃: you can "what"? My point is - don't add more columns into `GROUP BY` than you need according to the query requirements. If you need more columns - use `INNER JOIN` — zerkms, Dec 15 '13 at 21:12
@xQbert that's only if [MySQL ONLY_FULL_GROUP_BY is disabled](http://dev.mysql.com/doc/refman/5.6/en/group-by-extensions.html) — John Woo, Dec 15 '13 at 21:12
@zerkms Oh i see now, it should be commented as "You need to include all *NonAggregated* columns in the `GROUP BY` clause that is included in your `SELECT` clause". -- is it ok now? — John Woo, Dec 15 '13 at 21:15
@今草顿 웃: I don't think so. I personally don't like adding more `GROUP BY` columns since it may cause performance issues. If you need to do that - it's a time to rethink the design. — zerkms, Dec 15 '13 at 21:17
If you have multiple records in RENTAL or MEMBER_REGISTRATION per STAFF record then your query is probably giving you the wrong numbers anyway. — David Aldridge, Dec 15 '13 at 21:40

MT0 · Accepted Answer · 2013-12-15T21:39:42.770

You can rewrite the query using correlated sub-queries:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE STAFF ( StaffID NUMBER, Name VARCHAR2(50), BranchID NUMBER );
CREATE TABLE BRANCH ( BranchID NUMBER, Name VARCHAR2(50) );
CREATE TABLE RENTAL ( StaffID NUMBER );
CREATE TABLE MEMBER_REGISTRATION ( StaffID NUMBER );

INSERT INTO STAFF VALUES ( 1, 'Alice', 1 );
INSERT INTO STAFF VALUES ( 2, 'Bob',   2 );
INSERT INTO STAFF VALUES ( 3, 'Carol', 1 );
INSERT INTO STAFF VALUES ( 4, 'Dave',  2 );

INSERT INTO BRANCH VALUES ( 1, 'Branch A' );
INSERT INTO BRANCH VALUES ( 2, 'Branch B' );

INSERT INTO RENTAL VALUES ( 1 );
INSERT INTO RENTAL VALUES ( 1 );
INSERT INTO RENTAL VALUES ( 1 );
INSERT INTO RENTAL VALUES ( 1 );
INSERT INTO RENTAL VALUES ( 2 );
INSERT INTO RENTAL VALUES ( 2 );

INSERT INTO MEMBER_REGISTRATION VALUES ( 1 );
INSERT INTO MEMBER_REGISTRATION VALUES ( 1 );
INSERT INTO MEMBER_REGISTRATION VALUES ( 1 );
INSERT INTO MEMBER_REGISTRATION VALUES ( 3 );
INSERT INTO MEMBER_REGISTRATION VALUES ( 3 );
INSERT INTO MEMBER_REGISTRATION VALUES ( 3 );

Query 1:

    SELECT s.StaffID,
           s.Name,
           b.Name AS branch_name,
           ( SELECT COUNT(r.StaffID)
             FROM   RENTAL r
             WHERE  r.StaffID = s.StaffID ) AS rental_count,
           ( SELECT COUNT(m.StaffID)
             FROM   MEMBER_REGISTRATION m
             WHERE  m.StaffID = s.StaffID ) AS member_regestration_count
    FROM   STAFF s
           INNER JOIN BRANCH b
             ON s.BranchID = b.BranchID

Results:

| STAFFID |  NAME | BRANCH_NAME | RENTAL_COUNT | MEMBER_REGESTRATION_COUNT |
|---------|-------|-------------|--------------|---------------------------|
|       1 | Alice |    Branch A |            4 |                         3 |
|       2 |   Bob |    Branch B |            2 |                         0 |
|       3 | Carol |    Branch A |            0 |                         3 |
|       4 |  Dave |    Branch B |            0 |                         0 |

Edit 1: Removed COALESCE() as its not necessary.

Edit 2: Added column alias for BRANCH.name as there were two columns named "name".

score 1 · Answer 2 · answered Dec 15 '13 at 21:15

Just include the extra unaggregated columns in the group by:

SELECT STAFF.StaffID,
       STAFF.Name,
       BRANCH.Name,
       COUNT(RENTAL.StaffID),
       COUNT(MEMBER_REGISTRATION.StaffID)
FROM   STAFF
       LEFT OUTER JOIN RENTAL
         ON STAFF.StaffID = RENTAL.StaffID
       LEFT OUTER JOIN MEMBER_REGISTRATION
         ON STAFF.StaffID = MEMBER_REGISTRATION.StaffID
       INNER JOIN BRANCH
         ON STAFF.BranchID = BRANCH.BranchID
GROUP  BY STAFF.STaffID, STAFF.NAME, BRANCH.NAME;

Note, though, that this query will probably not do what you want, because you will get a cartesian product between the rental and member_registration records. You may want:

SELECT s.StaffID, s.Name, b.Name, r.NumRentals, mr.NumRegis
FROM   STAFF s LEFT OUTER JOIN
       (select StaffId, count(*) as NumRentals
        from RENTAL
        group by StaffId
       ) r
       ON s.StaffID = r.StaffID left outer join
       (select StaffId, count(*) as NumRegis
        from MEMBER_REGISTRATION
        group by StaffId
       ) mr
       ON s.StaffId = mr.StaffID LEFT OUTER JOIN
       BRANCH b
       ON s.BranchID = b.BranchID;

Note that this doesn't require aggregation on the outer query.

Rogue downvote? Or is there actually an issue with this answer? — Gordon Linoff, Dec 15 '13 at 21:22

Not a group by expression SQL error

2 Answers2