none
Fetch total salary for each department and manager salary also where in manager may not belongs to same dept RRS feed

  • Question

  • I have to Fetch total salary for each department and manager salary also where in manager may not belongs to same dept

    CREATE TABLE [dbo].[DEPT]
    (DEPTNO INT PRIMARY KEY ,
    DNAME VARCHAR(20),
    LOC VARCHAR(20) );
    GO

    INSERT INTO [dbo].[DEPT] VALUES (10, 'ACCOUNTING', 'NEW YORK');
    INSERT INTO [dbo].[DEPT] VALUES (20, 'RESEARCH', 'DALLAS');
    INSERT INTO [dbo].[DEPT] VALUES (30, 'SALES', 'CHICAGO');
    INSERT INTO [dbo].[DEPT] VALUES (40, 'OPERATIONS', 'BOSTON');


    CREATE TABLE [dbo].[EMP]
    (EMPNO INT PRIMARY KEY,
    ENAME VARCHAR(20),
    JOB VARCHAR(20),
    MGR INT,
    HIREDATE DATE,
    SAL MONEY,
    COMM MONEY,
    DEPTNO INT FOREIGN KEY REFERENCES DEPT(DEPTNO) );
    GO

    INSERT INTO [dbo].[EMP] VALUES
    (7369, 'SMITH', 'CLERK', 7902, '17-DEC-1980', 800, NULL, 20);
    INSERT INTO [dbo].[EMP] VALUES
    (7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-1981', 1600, 300, 30);
    INSERT INTO [dbo].[EMP] VALUES
    (7521, 'WARD', 'SALESMAN', 7698, '22-FEB-1981', 1250, 500, 30);
    INSERT INTO [dbo].[EMP] VALUES
    (7566, 'JONES', 'MANAGER', 7839, '2-APR-1981', 2975, NULL, 20);
    INSERT INTO [dbo].[EMP] VALUES
    (7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-1981', 1250, 1400, 30);
    INSERT INTO [dbo].[EMP] VALUES
    (7698, 'BLAKE', 'MANAGER', 7839, '1-MAY-1981', 2850, NULL, 30);
    INSERT INTO [dbo].[EMP] VALUES
    (7782, 'CLARK', 'MANAGER', 7839, '9-JUN-1981', 2450, NULL, 10);
    INSERT INTO [dbo].[EMP] VALUES
    (7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, NULL, 20);
    INSERT INTO [dbo].[EMP] VALUES
    (7839, 'KING', 'PRESIDENT', NULL, '17-NOV-1981', 5000, NULL, 10);
    INSERT INTO [dbo].[EMP] VALUES
    (7844, 'TURNER', 'SALESMAN', 7698, '8-SEP-1981', 1500, 0, 30);
    INSERT INTO [dbo].[EMP] VALUES
    (7876, 'ADAMS', 'CLERK', 7788, '12-JAN-1983', 1100, NULL, 20);
    INSERT INTO [dbo].[EMP] VALUES
    (7900, 'JAMES', 'CLERK', 7698, '3-DEC-1981', 950, NULL, 30);
    INSERT INTO [dbo].[EMP] VALUES
    (7902, 'FORD', 'ANALYST', 7566, '3-DEC-1981', 3000, NULL, 20);
    INSERT INTO [dbo].[EMP] VALUES
    (7934, 'MILLER', 'CLERK', 7782, '23-JAN-1982', 1300, NULL, 10); 



    • Edited by Usha1607 Monday, January 6, 2020 8:21 AM
    Monday, January 6, 2020 7:41 AM

All replies

  • Please check below to get salary based on dept. How are you defining managers? If possible, please provide create and insert scripts. 

    CREATE TABLE Emp ([empid] INT, [Did] INT, [ename] VARCHAR(3), [esal] INT);
    
    INSERT INTO Emp ([empid], [Did], [ename], [esal])
    VALUES (101, 10, 'abc', 10000), (102, 20, 'xyz', 10000), 
    (103, 20, 'ppp', 20000), (104, 10, 'qqq', 30000); CREATE TABLE Dept ([Did] INT, [dname] VARCHAR(5)); INSERT INTO Dept ([Did], [dname]) VALUES (10, 'dept1'), (20, 'dept2') SELECT d.did, d.dname, sum(esal) FROM Dept AS d INNER JOIN Emp AS e ON d.Did = e.Did GROUP BY d.Did, d.dname


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Monday, January 6, 2020 8:11 AM
  • Hi 

    Updated scripts

    Monday, January 6, 2020 8:23 AM
  • Hi Usha1607

    SELECT DEPT.DNAME, 
           emp.ENAME, 
           SUM(sal) OVER(PARTITION BY emp.empno) each_employee, 
           SUM(sal) OVER(PARTITION BY dept.deptno) each_department
    FROM [dbo].[EMP]
         JOIN DEPT ON emp.DEPTNO = dept.DEPTNO;

    I hope my script help to you

    Best Regards,

    Natig


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Monday, January 6, 2020 8:35 AM