Sum created columns at runtime mysql

Abdu Abdul

i want to sum up values of a row in a single column using MySQL. I have tried all the below codes but nothing seems to work, i need help please

What I have tried:

SELECT employee.dept,employee.loc, employee.work, 
COUNT(IF(employee_info.emp_date = '2021-11-01', employee_info.emp_date, NULL)) AS a,
 COUNT(IF(employee_info.emp_date = '2021-11-02', employee_info.emp_date, NULL)) AS b,
  COUNT(IF(employee_info.emp_date = '2021-11-02', employee_info.emp_date, NULL)) AS c, SUM("a"+"b"+"c") AS total FROM employee_info INNER JOIN employee ON employee_info.employee_number = employee.employee_number GROUP BY employee.dept WITH ROLLUP

SELECT employee.dept,employee.loc, employee.work, 
COUNT(IF(employee_info.emp_date = '2021-11-01', employee_info.emp_date, NULL)) AS a,
 COUNT(IF(employee_info.emp_date = '2021-11-02', employee_info.emp_date, NULL)) AS b,
  COUNT(IF(employee_info.emp_date = '2021-11-02', employee_info.emp_date, NULL)) AS c, SUM('a'+'b'+'c') AS total FROM employee_info INNER JOIN employee ON employee_info.employee_number = employee.employee_number GROUP BY employee.dept WITH ROLLUP

SELECT employee.dept,employee.loc, employee.work, 
COUNT(IF(employee_info.emp_date = '2021-11-01', employee_info.emp_date, NULL)) AS a,
 COUNT(IF(employee_info.emp_date = '2021-11-02', employee_info.emp_date, NULL)) AS b,
  COUNT(IF(employee_info.emp_date = '2021-11-02', employee_info.emp_date, NULL)) AS c, 'a'+'b'+'c' AS total FROM employee_info INNER JOIN employee ON employee_info.employee_number = employee.employee_number GROUP BY employee.dept WITH ROLLUP

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke4y ago
Accepted answer
Disclaimer first, I have NO knowledge of MySQL.

Having said that, from whatever experieace I have using Microsoft SQL Server (which is a lot) i can say that it doesn't work you expect it to work.

The column names used in the query are NOT available in same query.

You will need to do it as follows, even if it looks / sounds silly and couter intuitive.
SELECT employee.dept
  ,employee.loc
  ,employee.work
  ,COUNT(IF(employee_info.emp_date = '2021-11-01', employee_info.emp_date, NULL)) AS a
  ,COUNT(IF(employee_info.emp_date = '2021-11-02', employee_info.emp_date, NULL)) AS b
  ,COUNT(IF(employee_info.emp_date = '2021-11-03', employee_info.emp_date, NULL)) AS c
  ,COUNT(IF(employee_info.emp_date = '2021-11-01', employee_info.emp_date, NULL)) +
    COUNT(IF(employee_info.emp_date = '2021-11-02', employee_info.emp_date, NULL)) +
    COUNT(IF(employee_info.emp_date = '2021-11-03', employee_info.emp_date, NULL)) AS total
FROM employee_info
INNER JOIN employee ON employee_info.employee_number = employee.employee_number
GROUP BY employee.dept WITH ROLLUP
I have no idea what you are up to, but there is better way to do this. You can also use CASE expression.
Let me know the output you expect and I might be able to help you.

Hope it helps!
Muhammad Imran Ansari4y ago
You can also use scaler function to break your query and write seperate logic to get Total value.
Abdu Abdul4y ago
I tried your answer @Nitin Sontakke it worked fine, but the query is a little bit long