Converting Query into SQL Server syntax

Question

I've been getting help from Chegg and most answers given to me don't work for me. My first task is write a query where it list the project with the most hours.

One response was:

Select project.Proj_ID , proj_Name from Project inner join work_period on Project.Proj_ID = work_period.Proj_Id group by project.Proj_ID , proj_Name having count(weekly_work_hrs) = max(count(weekly_work_hrs));

Error I get:

Msg 130, Level 15, State 1, Line 1 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Second Response is

CREATE VIEW project_with_most_hours AS
select w1.proj_id
from work_period w1   
group by w1.proj_id   
having sum(w1.weekly_work_hrs) = (select max(sum(w2.weekly_work_hrs)) from work_period w2 group by w2.proj_id);

Error I get is:

Msg 130, Level 15, State 1, Procedure project_with_most_hours, Line 5 [Batch Start Line 0] Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

The end result im looking for is for the console to display only 1 Proj_ID or proj_name that has the the most worked hours from summing all the worked hours of the employees that worked in that project.

Any help on how to resolve either one of these queries is greatly appreciated

Here is the script to create my database if it helps

create table department
(
  dept_ID int not null ,
  dept_name char(50) NOT NULL,
  manager_ID int not null,
  manager_start_date date not null,

  constraint Dept_PK primary key (dept_ID),
  constraint D_Name_AK unique (dept_name)
);
insert into department values(1,'abc',1,'2019-01-08');
insert into department values(2,'abc2',2,'2019-01-08');
insert into department values(3,'abc3',2,'2019-01-08');
insert into department values(4,'abc4',2,'2019-01-08');

/*project table done*/
create table project
(
  proj_ID int not null,
  proj_name varchar(20) not null,
  dept_ID int not null,
  proj_location varchar(20) not null,

  constraint Proj_ID_PK primary key (proj_ID),
  constraint Proj_Dep_FK foreign key (dept_ID) references department(dept_ID)
);
insert into project values ( 1,'project1',1,'india');
insert into project values ( 2,'project2',2,'US');

/*employee table done*/
create table employee
(
  emp_ID int NOT NULL ,
  emp_name char(50) not null,
  emp_ssn char(11) not null,
  emp_address char(50) not null,
  salary decimal(10,2) not null,
  sex char(1) not null,
  date_of_birth date not null,
  dept_ID int not null,
  supervisor_ID int null,

  constraint emp_PK primary key(emp_ID),
  constraint emp_Name_AK unique (emp_name),
  constraint emp_SSN_AK unique (emp_ssn),
  constraint sup_FK foreign key(supervisor_ID) references employee(emp_ID),
  constraint empDep_FK foreign key(dept_ID) references department(dept_ID)
);

insert into employee values( 1,'jagmeet', 'ssn','patel nagar',300,'M','1997-07-01',1,1);

insert into employee values( 2,'harpreet', 'ssn1','patel nagar2',300,'F','1997-07-01',1,2);
/*Department location table done*/
create table dept_location
(
  dept_ID int not null,
  location char(50) not null,

  constraint dept_location_PK primary key(dept_ID, location),
  constraint dept_FK foreign key (dept_ID) references department(dept_ID)
);
insert into dept_location values(1,'loc1');

insert into dept_location values(2,'loc2');

/*dependent table done*/
create table dependent
(
  dependent_ID int not null ,
  emp_ID int NOT NULL,
  dependent_name varchar(20) NOT NULL,
  dependent_sex char(1) NOT NULL,
  dependent_DOB date not null,
  dep_relation varchar(10) not null,

  constraint dep_ID_PK primary key (dependent_ID),
  constraint deb_emp_ID_FK foreign key (emp_ID) references employee(emp_ID)
);
insert into dependent values (1,2,'deptname','M','2018-01-09','rel1');

insert into dependent values (2,1,'deptname2','F','2018-01-09','rel2');
/*work period table done*/

create table work_period
(
  emp_ID int NOT NULL,
  proj_ID int not null,
  pay_period date not null,
  weekly_work_hrs int not null,

  constraint pay_period_PK primary key (pay_period),
  constraint WP_empID_FK foreign key (emp_ID) references employee(emp_ID),
  constraint WP_projID_FK foreign key (proj_ID) references project(proj_ID)
);
insert into work_period values(1,1,'2012-09-01',7);

insert into work_period values(2,2,'2014-09-01',8);

Answer 1

You can query, aggregate and just take TOP 1

SELECT TOP 1 WITH TIES
      P.Proj_ID,
      P.proj_Name,
      SUM( WP.weekly_work_hrs) sumofhrs
   FROM 
      Project P
         JOIN work_period WP
            ON P.Proj_ID = WP.Proj_Id
   GROUP BY 
      P.Proj_ID,
      P.proj_Name
   order by
      SUM( WP.weekly_work_hrs) DESC

Answer 2

using CTE, query to show only data with the matching max weekly work hours

;
WITH cte
AS (SELECT
  project.Proj_ID,
  proj_Name,
  SUM(weekly_work_hrs) sumofhrs
FROM Project
INNER JOIN work_period
  ON Project.Proj_ID = work_period.Proj_Id
GROUP BY project.Proj_ID,
         proj_Name)
SELECT
  *
FROM cte
WHERE sumofhrs = (SELECT
  MAX(sumofhrs)
FROM cte)

Answer 3

You can ORDER BY the sum of hours descending and pick the top result only with TOP 1. If you want to show all the top projects in case of a tie use the WITH TIES option to TOP. Otherwise omit the WITH TIES to (randomly) pick one in case of a tie. You might also want to LEFT JOIN instead of an INNER JOIN so that, if you have only projects no one has worked on yet they get outputted anyway -- zero hours is technically also valid maximum.

SELECT TOP 1
           WITH TIES
       p.proj_id,
       p.proj_name
       FROM project p
            LEFT JOIN work_period wp
                      ON wp.proj_id = p.proj_id
       GROUP BY p.proj_id,
                p.proj_name
       ORDER BY sum(coalesce(wp.weekly_work_hrs, 0)) DESC;