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.
SQL
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
;
SQL
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 Text
select COALESCE(job, 'total') job, sum(sal) sal
from emps
group by job
with ROLLUP; With this you will get result like below
| ANALYST | 7320 |
| CLERK | 4920 |
| MANAGER | 8275 |
| PRESIDENT | 5000 |
| SALESMAN | 5600 |
| total | 31115 |
If want to use union All operator to get a descriptive result we can also do that lets check the below example.
SQL
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
| 10 | MANAGER | Total By Dept and Job | 2450 |
| 10 | PRESIDENT | Total By Dept and Job | 5000 |
| 20 | ANALYST | Total By Dept and Job | 7320 |
| 20 | CLERK | Total By Dept and Job | 2252 |
| 20 | MANAGER | Total By Dept and Job | 2975 |
| 30 | CLERK | Total By Dept and Job | 1368 |
| 30 | MANAGER | Total By Dept and Job | 2850 |
| 30 | SALESMAN | Total By Dept and Job | 5600 |
| null | ANALYST | Total By Job | 7320 |
| null | CLERK | Total By Job | 4920 |
| null | MANAGER | Total By Job | 8275 |
| null | PRESIDENT | Total By Job | 5000 |
| null | SALESMAN | Total By Job | 5600 |
| 10 | null | Total By Dept | 8750 |
| 20 | null | Total By Dept | 12547 |
| 30 | null | Total By Dept | 9818 |
| null | null | Grand Total | 31115 |
Hopefully that help you to solve your problem in any of your next project. 😊
