CODEWITHSUNDEEP
mysqlsql
Ghost
Ghost

Reputation: 23

SQL Query fails to function when using group by

I built this query to show me how many employees and customers to a branch, it gives me duplicates therefore I need to group them. However, the query is invalid when I add the group by. Anyone have any idea why?

  SELECT B.branchID, B.branchName, COUNT(E.staffNo) AS "No. Of Employees", COUNT(C.customerID) AS "No. Of Customers"
        FROM Branch B, Employee E, Customer C
        WHERE E.employeeBranchID = B.branchID
        AND B.branchID = C.customerBranchID
        GROUP BY B.branchID;

Upvotes: 0

Views: 579

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

This query would be better written as:

SELECT B.branchID, B.branchName, COUNT(E.staffNo) AS "No. Of Employees", COUNT(C.customerID) AS "No. Of Customers"
FROM Branch B JOIN
     Employee E
     ON E.employeeBranchID = B.branchID JOIN
     Customer C
     ON B.branchID = C.customerBranchID
GROUP BY B.branchID, B.branchName;

If you are in ANSI mode, then MySQL behaves like most other databases and will return an error because B.branchName is unaggregated.

Notes:

  • Use explicit JOIN syntax. Never use commas in the FROM clause.
  • All non-aggregated columns are in the GROUP BY.
  • Your original query is ANSI compliant if branchId is declared as the primary or a unique key on branch. However, most databases still do not accept the query.

Upvotes: 2

Priyanshu
Priyanshu

Reputation: 881

SELECT B.branchID, B.branchName, COUNT(E.staffNo) AS "No. Of Employees", COUNT(C.customerID) AS "No. Of Customers" FROM Branch B left join Employee E on E.employeeBranchID = B.branchID left join Customer C on B.branchID = C.customerBranchID GROUP BY B.branchID;

Upvotes: 0

Ermintar
Ermintar

Reputation: 1393

SELECT B.branchID, B.branchName, COUNT(E.staffNo) AS "No. Of Employees", COUNT(C.customerID) AS "No. Of Customers"
    FROM Branch B, Employee E, Customer C
    WHERE E.employeeBranchID = B.branchID
    AND B.branchID = C.customerBranchID
    GROUP BY B.branchID, B.branchName;

Most DB require the list of group by items == the list of items in select before an aggregation function

Upvotes: 0

Related Questions