Errata

I am working my way through SQL Cookbook, 2nd Edition on oreilly.com, and I believe I’ve found an error in the solution for section "3.9 Performing Joins When Using Aggregates."

It looks like it would be page 54 in the PDF/printed version.

Specifically, the solutions:

1 select deptno,
2 sum(distinct sal) as total_sal,
3 sum(bonus) as total_bonus
4 from (
5 select e.empno,
6 e.ename,
7 e.sal,
8 e.deptno,
9 e.sal*case when eb.type = 1 then .1
10 when eb.type = 2 then .2
11 else .3
12 end as bonus
13 from emp e, emp_bonus eb
14 where e.empno = eb.empno
15 and e.deptno = 10
16 ) x
17 group by deptno

And

1 select distinct deptno,total_sal,total_bonus
2 from (
3 select e.empno,
4 e.ename,
5 sum(distinct e.sal) over
6 (partition by e.deptno) as total_sal,
7 e.deptno,
8 sum(e.sal*case when eb.type = 1 then .1
9 when eb.type = 2 then .2
10 else .3 end) over
11 (partition by deptno) as total_bonus
12 from emp e, emp_bonus eb
13 where e.empno = eb.empno
14 and e.deptno = 10
15 ) x

Both work for the sample table provided in the book, but they both fail if two employees in department 10 have the same salary.

I verified this by increasing employee 7934’s salary to $5000 to match employee 7839’s salary:

update emp e set sal = 5000.00 where empno = 7934;

which should have resulted in a net gain of $5000 - $1300 = $3700, but instead the department total decreased from $8750 to $7450, as I expected, since employee 7934’s $1300 salary isn’t included anymore, but the $5000 only gets counted once, even though two employees have that salary.

The correct way to compute this is to sum the bonus in the inner query and then sum the salary in the outer:

SELECT deptno,
SUM(sal) AS total_sal,
SUM(bonus) AS total_bonus
FROM (SELECT e.empno,
e.ename,
e.sal,
e.deptno,
SUM(e.sal * CASE
WHEN eb.type = 1 THEN
.1
WHEN eb.type = 2 THEN
.2
ELSE
.3
END) AS bonus
FROM emp e,
emp_bonus eb
WHERE e.empno = eb.empno
AND e.deptno = 10
GROUP BY e.empno,
e.ename,
e.sal,
e.deptno) x
GROUP BY deptno

For the other query, I tried to figure out a way to write it without a subquery, but best I can do is pretty much the same thing as above, except using DISTINCT and PARTITION BY. And since the inner records are filtered by deptno = 10, the outer PARTITION BY is kind of useless.

SELECT DISTINCT deptno,
SUM(sal) OVER(PARTITION BY deptno) AS total_sal,
SUM(emp_bonus) OVER(PARTITION BY deptno) AS total_bonus
FROM (SELECT DISTINCT e.empno,
e.ename,
e.sal,
e.deptno,
SUM(e.sal * CASE
WHEN eb.type = 1 THEN
.1
WHEN eb.type = 2 THEN
.2
ELSE
.3
END) OVER(PARTITION BY e.empno) AS emp_bonus
FROM emp e,
emp_bonus eb
WHERE e.empno = eb.empno
AND e.deptno = 10) x

Or this, which really isn’t any better:

SELECT DISTINCT e.deptno,
SUM(e.sal) OVER(PARTITION BY e.deptno) AS total_sal,
SUM(e.sal * x.emp_bonus_mult) OVER(PARTITION BY e.deptno) AS total_bonus
FROM emp e,
(SELECT DISTINCT eb.empno,
SUM(CASE
WHEN eb.type = 1 THEN
.1
WHEN eb.type = 2 THEN
.2
ELSE
.3
END) OVER(PARTITION BY eb.empno) AS emp_bonus_mult
FROM emp_bonus eb) x
WHERE e.empno = x.empno
AND e.deptno = 10

There is no MySQL recipe for 6.5 Separating numeric and character data

The solution does not print out the second period on the initial

Passing the earliest of the two dates in the function (as the book instructs) results in a negative value. several errors in this book

I get an error when running the solution for MySQL recipe 8.5. After reviewing the error message and the MySQL reference manual, the DATEDIFF function does not accept an interval parameter as the book states it does.

Recipe states to use datepart function but there is not date part function for MySQL

Recipe states to use the getdate function but there is no getdate function in MySQL

No MySQL solution or discussion provided for recipe 10.5

Recipe 11.11 does not have a solution section for MySQL

For recipe 11.12, there is a "DB2, MySQL, and SQL Server" solution but there is also a separate section saying that there isn't a MySQL solution (page 359)

The book states that "The CEIL function will return the smallest whole number greater than the value passed to it". The ceil function however will return the smallest integer that is greater than or equal to the number passed to the function. The equal portion of that statement is especially relevant for this solution,

On page 420 (recipe 12.14 solution section) the book states "As of the time of this writing, MySQL doesn't support either CUBE or GROUPING." Then on page 430 (recipe 12.20 solution section) GROUPING is used in the MySQL solution. Many errors in this book I paid for.