none
SQL help

    Question

  • Hi,

    I am writing one sql like

    SELECT SUM(x.Salary) 
     FROM Salary x
          INNER JOIN Employee y
          ON x.EMPID = y.EMPID
          WHERE  y.[EmpName] IN ('Joy', 'Smith')
          GROUP BY y.[EmpName]

    Which is giving me two sales...i.e one for Joy and another for Smith but I want to write SQL to get the SUM of both.

    Tuesday, July 09, 2013 1:21 PM

Answers

  • SELECT SUM (S.salary_amt) 
     FROM Payroll AS S,
    Personnel AS P
    WHERE P.emp_id = S.emp_id
      AND P.emp_name IN ('JOY', 'SMITH');


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, July 09, 2013 1:38 PM
  • Try dropping the GROUP BY:

    SELECT SUM(x.Salary) 
    FROM Salary x
    INNER JOIN Employee y
    ON x.EMPID = y.EMPID
    WHERE  y.[EmpName] IN ('Joy', 'Smith')
    Tuesday, July 09, 2013 1:39 PM

All replies

  • SELECT SUM (S.salary_amt) 
     FROM Payroll AS S,
    Personnel AS P
    WHERE P.emp_id = S.emp_id
      AND P.emp_name IN ('JOY', 'SMITH');


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, July 09, 2013 1:38 PM
  • Try dropping the GROUP BY:

    SELECT SUM(x.Salary) 
    FROM Salary x
    INNER JOIN Employee y
    ON x.EMPID = y.EMPID
    WHERE  y.[EmpName] IN ('Joy', 'Smith')
    Tuesday, July 09, 2013 1:39 PM
  • Remove the group by clause in your select statement.

    SELECT SUM(x.Salary) 
     FROM Salary x
          INNER JOIN Employee y
          ON x.EMPID = y.EMPID
          WHERE  y.[EmpName] IN ('Joy', 'Smith')
    

    SUM aggregates per group by, so by grouping by the Empname, it would give you a figure for each.

    Thanks.

    Tuesday, July 09, 2013 1:40 PM