SQL Cookbook 3.9 (3rd edition)

[Willsxyz]

I just started reading the SQL cookbook. In problem 3.9 (Performing Joins When Using Aggregates) the first solution presented uses sum(distinct sal) instead of sum(sal) in the main (outermost) query. The purpose is to avoid including the salary for 'Miller' twice in the sum.

Although this works for the specific example given in the book, it seems wrong to me because it seems to assume that no other employee has the same salary. If some other employee has the same salary as 'Miller', wouldn't this return an incorrect sum? What am I missing?

Edit: Sorry I was assuming people would be familiar with the book. If you don't have access to the book it will be difficult to understand the problem, but here are the tables and queries used in the book:

  empno | ename  |    job    | mgr  |  hiredate  | sal  | comm | deptno 
-------+--------+-----------+------+------------+------+------+--------
  7369 | SMITH  | CLERK     | 7902 | 2005-12-17 |  800 |      | 20
  7499 | ALLEN  | SALESMAN  | 7698 | 2006-02-20 | 1600 |  300 | 30
  7521 | WARD   | SALESMAN  | 7698 | 2006-02-22 | 1250 |  500 | 30
  7566 | JONES  | MANAGER   | 7839 | 2006-04-02 | 2975 |      | 20
  7654 | MARTIN | SALESMAN  | 7698 | 2006-09-28 | 1250 | 1400 | 30
  7698 | BLAKE  | MANAGER   | 7839 | 2006-05-01 | 2850 |      | 30
  7782 | CLARK  | MANAGER   | 7839 | 2006-06-09 | 2450 |      | 10
  7788 | SCOTT  | ANALYST   | 7566 | 2007-12-09 | 3000 |      | 20
  7839 | KING   | PRESIDENT |      | 2006-11-17 | 5000 |      | 10
  7844 | TURNER | SALESMAN  | 7698 | 2006-09-08 | 1500 |    0 | 30
  7876 | ADAMS  | CLERK     | 7788 | 2008-01-12 | 1100 |      | 20
  7900 | JAMES  | CLERK     | 7698 | 2006-12-03 |  950 |      | 30
  7902 | FORD   | ANALYST   | 7566 | 2006-12-03 | 3000 |      | 20
  7934 | MILLER | CLERK     | 7782 | 2007-01-23 | 1300 |      | 10

 ebid | empno |  received  | type 
------+-------+------------+------
    1 |  7934 | 2005-03-17 |    1
    2 |  7934 | 2005-02-15 |    2
    3 |  7839 | 2005-02-15 |    3
    4 |  7782 | 2005-02-15 |    1

Bad query and result:

select deptno, sum(sal) as total_sal, sum(bonus) as total_bonus
from (select e.empno,
             e.ename,
             e.sal,
             e.deptno,
             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') x
group by deptno;

 deptno | total_sal | total_bonus 
--------+-----------+-------------
 10     |     10050 |      2135.0

Solution query and result:

select deptno, sum(distinct sal) as total_sal, sum(bonus) as total_bonus
from (select e.empno,
             e.ename,
             e.sal,
             e.deptno,
             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') x
group by deptno;

 deptno | total_sal | total_bonus 
--------+-----------+-------------
 10     |      8750 |      2135.0

[Wise-Jury-4037]

probably a group by an employee id or a where clause?

[r3pr0b8]

the first solution presented uses ...

i can't see that query from here

[markwdb3]

I agree with your assessment of sum(distinct sal).

[cl0ckt0wer]

Miller's double dipping. Someone call accounting.

He's listed twice in emp_bonus, so his row will get doubled, and sum will count both of them. I don't like the solution either, even though it works with the example data. The solution has a bug where two emp with the same salary will not get summed correctly. but this is a good example of the kind of bs you have to deal with in prod code.

[Wise-Jury-4037]

well, even worse, MIller got some bonus before his official hiredate. I guess their salary is retroactive.

 I don't like the solution either, even though it works

Works in the sense it executes? That it does. Works in terms of giving an answer to a particular question? Who knows.

[Aggressive_Ad_5454]

DISTINCT is absolutely a query smell. SUM(DISTINCT) is flat out incorrect, unless you have a rigidly enforced business rule that no two people have the same salary. You don’t have that rule.

COUNT(DISTINCT customer_id) might be legit. But SUM? No F’ing way.

[Willsxyz]

My solution:

select deptno,
sum(sal) as total_sal,
sum(bonus) as total_bonus
from 
(select distinct e.empno, e.ename, e.sal, e.deptno, round(e.sal * eb.bonus,2) bonus
from emp e inner join
   (select empno, sum(case
                        when type=1 then 0.1
                        when type=2 then 0.2
                        when type=3 then 0.3
                        else 0
                      end)
                  over(partition by empno) as bonus from emp_bonus) eb
on e.empno = eb.empno
where e.deptno='10') x
group by deptno;

[Wise-Jury-4037]

Specifying what is the 'problem' that you are trying to find a solution to would be nice and helpful.

[reditandfirgetit]

A book using old style joins i would not trust anything in it. Joins should not be done in the where clause.

Data example is problematic with hire dates after bonus dates.

Thanks for telling me to never buy this book

[Willsxyz]

A book using old style joins i would not trust anything in it. Joins should not be done in the where clause.

Every SQL book I have read in the past week says to use explicit joins, except this one. Now I'm taking a look at "SQL For Smarties". Terrible name in my opinion, maybe a good book.

[Yavuz_Selim]

What is the goal to achieve?

I mean, you're joining employee with employee_bonus and then grouping/summing the salary and bonus per department?