CODEWITHSUNDEEP
mysqlaggregate-functionsmysql-workbench
QWERTY
QWERTY

Reputation: 263

Where clause with sum aggregate in sql query

I have a table and I want to display only those departments that have at least one registered user in them. My query is below:

SELECT departments.id as DepartmentID, 
       departments.depName as DepartmentName,
       SUM(IF(users.isDelete=0, 1, 0)) AS NumberOfUsers
FROM (myDB.departments) 
LEFT JOIN myDB.users ON departmentID=departments.id
AND `departments`.`isDelete` =  0
HAVING SUM(NumberOfUsers) >=0

The HAVING SUM(NumberOfUsers) >=0 is not working at all. I would like to check if the NumberOfUsers is more than one then display it if not more than one then do not display it. Any suggestions?

Upvotes: 1

Views: 106

Answers (3)

Zafar Malik
Zafar Malik

Reputation: 6854

I think you can simplify your query as you are checking condition in user table that is_delete should be 0, so there is no need of left join you can just use normal join. After this you are removing all rows those does not have record by having clause, so you can simply put this condition in where clause as per below-

SELECT dpt.id AS DepartmentID, dpt.depName AS DepartmentName, 
COUNT(usr.id) AS NumberOfUsers
FROM myDB.departments AS dpt 
JOIN myDB.users AS usr ON usr.departmentID=dpt.id
WHERE dpt.`isDelete` =  0 AND usr.isDelete=0

Note: Assuming users table have primary key as id, if not then you can use any other column in count function.

Upvotes: 1

Musa Haidari
Musa Haidari

Reputation: 2267

In this case you need to use the group by clause too.

 SELECT departments.id as DepartmentID, 
        departments.depName as DepartmentName,
        SUM(IF(users.isDelete=0, 1, 0)) AS NumberOfUsers
 FROM (myDB.departments) 
 LEFT JOIN myDB.users ON departmentID=departments.id
     AND `departments`.`isDelete` =  0
 GROUP BY departments.id, departments.depName
 HAVING SUM(IF(users.isDelete=0, 1, 0)) >=0

But if you want a shorter answer, you can use JOIN instead of LEFT JOIN and removing HAVING clause:

 SELECT departments.id as DepartmentID, 
        departments.depName as DepartmentName,
        SUM(IF(users.isDelete=0, 1, 0)) AS NumberOfUsers
 FROM (myDB.departments) 
 JOIN myDB.users ON departmentID=departments.id
     AND `departments`.`isDelete` =  0
 GROUP BY departments.id, departments.depName

Upvotes: 0

Joe Taras
Joe Taras

Reputation: 15389

Try this:

SELECT departments.id as DepartmentID, 
       departments.depName as DepartmentName,
       (SELECT COUNT(u.id)
       FROM users u
       WHERE u.departmentID = d.id) as NumberOfUsers
FROM departments d
WHERE d.isdelete = 0
AND EXISTS
  (SELECT 'user'
  FROM users u
  WHERE u.departmentID = d.id)

In this way, you don't use a SUM in main query (where you must use GROUP BY to show other scalar fields like departments.id and departments.depName. In My Sql is not mandatory GROUP BY)

EXISTS clause garantee the presence at least one user. If you want to show all department (indipendently number of users, remove EXISTS clause)

Upvotes: 1

Related Questions