> I want to report:
> 1. Which device/software that (practically) never shows up some problems.
select item from items_tbl
where item not in (select distinct item from problem_tbl);
(there may be more efficient ways to form this query)
> 2. A top 10 of the most occuring problems
select problem, count(problem) as num from problem_tbl
group by problem order by num desc limit 10;
> 3. The need of more time that a employee needs to solve a problem over the
> average solving time.
It is expensive to calculate an average from scratch each time you want
to compare a row with it. So I would suggest having a table which holds
the expected times for each problem (or problem type), which you can
then adjust as needed.
begin;
delete from timing_tbl;
select problem, avg(solution_time) as solution_time
into timing_tbl from problem_tbl
group by problem;
end;
select employee as stupid from problem_tbl p, timing_tbl t
where (t.problem = p.problem)
and (p.solution_time > 1.5*t.solution_time);
hth
- Thomas
