CODEWITHSUNDEEP
sql
user366312
user366312

Reputation: 16948

What would be the result of the following query?

enter image description here

The following query I tried...

select d.deptID, max(tt.total)
from dept d,
(select d.deptID, d.deptName, sum(days) as total
from vacation v, employee e, dept d
where v.empId = e.empID
and d.deptID = e.deptID
group by d.deptID, d.deptName) tt
where d.deptID = tt.deptID
group by d.deptName;
--having  max(tt.total);

Upvotes: 0

Views: 229

Answers (3)

StackUser
StackUser

Reputation: 5398

Try like this,

   SELECT TOP 1 d.departmentname,
             Sum(v.days) AS vacations
FROM   employee emp
       INNER JOIN department d
               ON d.departmentid = emp.departmentid
       INNER JOIN vacations v
               ON v.employeeid = emp.employeeid
GROUP  BY d.departmentname
ORDER  BY 2 DESC

Upvotes: 1

sagi
sagi

Reputation: 40481

Try using limit since your inner query already does the calculation.

select TOP 1 * from (
select d.deptID, d.deptName, sum(days) as total
from vacation v, employee e, dept d
where v.empId = e.empID
and d.deptID = e.deptID
group by d.deptID, d.deptName)
order by total desc;

Depends on the dbms you're using.. this is for mysql

In oracle use where rownum = 1

In sql server use SELECT TOP 1 *

Upvotes: 2

Haytem BrB
Haytem BrB

Reputation: 1499

Using Top:

Select top 1 with ties * from
(Select D.DepartmentName, sum(V.Days) as SumDays
from Vacations V
inner join Employee E on E.EmployeeID=V.EmployeeID
inner join Department D on D.DepartmentID=E.DepartmentID
group by D.DepartmentName)SumDays
Order by SumDays desc

Upvotes: 1

Related Questions