[MySQL] Calculate Total from Rows

If you are looking for calculate subtotal from result set. This recipe will help you to easy calculate total from the rows values.

For this recipe we are using ROLLUP extension with GROUP By clause that can solve this problem easily and perfectly.

Here we have a table that create table query is below and also we provide the insert record query that will save your time.

SQLCopy
CREATE TABLE `emps` (
	`EMPNO` INT(11) NOT NULL,
	`ENAME` VARCHAR(10) NULL DEFAULT NULL COLLATE 'armscii8_bin',
	`JOB` VARCHAR(9) NULL DEFAULT NULL COLLATE 'armscii8_bin',
	`MGR` INT(11) NULL DEFAULT NULL,
	`HIREDATE` DATE NULL DEFAULT NULL,
	`SAL` INT(11) NULL DEFAULT NULL,
	`COMM` INT(11) NULL DEFAULT NULL,
	`DEPTNO` INT(11) NULL DEFAULT NULL
)
COLLATE='armscii8_bin'
ENGINE=InnoDB
;
SQLCopy
INSERT INTO `emps` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 1152, NULL, 20);
INSERT INTO `emps` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
INSERT INTO `emps` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO `emps` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO `emps` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO `emps` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO `emps` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO `emps` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 4320, NULL, 20);
INSERT INTO `emps` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO `emps` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO `emps` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, NULL, 20);
INSERT INTO `emps` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 1368, NULL, 30);
INSERT INTO `emps` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO `emps` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);

Now we are ready to go to our main problem.

We will use aggregate function SUM to sum the salaries and use the ROLLUP to organize the results into subtotals by there job and a grand total to total rows. With SQL we can have the option to use the GROUPING function.

Plain TextCopy
select COALESCE(job, 'total') job, sum(sal) sal 
    from emps 
    group by job 
with ROLLUP; 

With this you will get result like below

ANALYST7320
CLERK4920
MANAGER8275
PRESIDENT5000
SALESMAN5600
total31115

If want to use union All operator to get a descriptive result we can also do that lets check the below example.

SQLCopy
select deptno, job, 'Total By Dept and Job' as category, sum(sal) as sal
from emps
group by deptno, job
union all
select null, job, 'Total By Job', sum(sal)
from emps
group by job
union all 
select deptno, null, 'Total By Dept', sum(sal)
from emps
group by deptno
union all
select null, null, 'Grand Total', sum(sal)
from emps;

Result Set

10MANAGERTotal By Dept and Job2450
10PRESIDENTTotal By Dept and Job5000
20ANALYSTTotal By Dept and Job7320
20CLERKTotal By Dept and Job2252
20MANAGERTotal By Dept and Job2975
30CLERKTotal By Dept and Job1368
30MANAGERTotal By Dept and Job2850
30SALESMANTotal By Dept and Job5600
nullANALYSTTotal By Job7320
nullCLERKTotal By Job4920
nullMANAGERTotal By Job8275
nullPRESIDENTTotal By Job5000
nullSALESMANTotal By Job5600
10nullTotal By Dept8750
20nullTotal By Dept12547
30nullTotal By Dept9818
nullnullGrand Total31115

Hopefully that help you to solve your problem in any of your next project. 😊