How to sum Salary column with a condition

Hi,
I'd like to sum the salary for the PayDate<'5/1/1985'. Can I use one SQL
statement to get it? Please show me the SQL statement. Thank a lot.
EmployeeNo PayDate Salary
1 1/31/1985 $1,000.00
1 2/28/1985 $1,000.00
1 3/31/1985 $1,000.00
1 4/30/1985 $1,000.00
1 5/31/1985 $1,000.00
1 6/30/1985 $1,000.00"chris" <yma16@.kicon.com> wrote in message
news:eYNiCgqGFHA.3916@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'd like to sum the salary for the PayDate<'5/1/1985'. Can I use one SQL
> statement to get it? Please show me the SQL statement. Thank a lot.
> EmployeeNo PayDate Salary
> 1 1/31/1985 $1,000.00
> 1 2/28/1985 $1,000.00
> 1 3/31/1985 $1,000.00
> 1 4/30/1985 $1,000.00
> 1 5/31/1985 $1,000.00
> 1 6/30/1985 $1,000.00
>
I assume that you want to do some grouping, or else you could simply use a
WHERE clause.
select EmployeeNo,
sum(case when PayDate<'5/1/1985' then Salary else 0 end) Salary
from MyTable
group by EmployeeNo
David|||Chris,
Try....
Select Sum(Salary) as 'Total Salary', EmployeeNo
from YourTable
where PayDate < '5/1/1985'
Group By EmployeeNo
Thanks
Barry|||Thank you both for the quick help.
"chris" <yma16@.kicon.com> wrote in message
news:eYNiCgqGFHA.3916@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'd like to sum the salary for the PayDate<'5/1/1985'. Can I use one
SQL
> statement to get it? Please show me the SQL statement. Thank a lot.
> EmployeeNo PayDate Salary
> 1 1/31/1985 $1,000.00
> 1 2/28/1985 $1,000.00
> 1 3/31/1985 $1,000.00
> 1 4/30/1985 $1,000.00
> 1 5/31/1985 $1,000.00
> 1 6/30/1985 $1,000.00
>

how to subquery with 2 or multiple columns

This is a small problem .. hope I could find quick answer :)
I am trying to write a query which uses a subquery in its where
condition. there are 2 columns (both PK) for which checking is
required.
I can run this query in DB2 with no problems. what is the correct
approach to do that in SQL Server 2005 ?
SELECT EMP_ID,DEPT_ID
FROM
STAGE_RMRS_PPLSFT
WHERE
(EMP_ID,DEPT_ID)
NOT IN
(SELECT MANAGER_ID,MGR_DEPT_ID
FROM
STAGE_RMRS_PPLSFT
)
Note : I did managed to do the same in SQL with combining the 2
columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
as index scan doesn't apply here.
pls suggest me a better solutionAs you've noticed, SQL Server doesn't allow multiple columns in IN (row-valued constructors). I
suggest you use EXISTS instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ashish Prasad" <ashishbitm@.gmail.com> wrote in message
news:97e9525a-73c6-4b5d-a39e-e0d872779f48@.d70g2000hsb.googlegroups.com...
> This is a small problem .. hope I could find quick answer :)
> I am trying to write a query which uses a subquery in its where
> condition. there are 2 columns (both PK) for which checking is
> required.
> I can run this query in DB2 with no problems. what is the correct
> approach to do that in SQL Server 2005 ?
> SELECT EMP_ID,DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> WHERE
> (EMP_ID,DEPT_ID)
> NOT IN
> (SELECT MANAGER_ID,MGR_DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> )
> Note : I did managed to do the same in SQL with combining the 2
> columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
> as index scan doesn't apply here.
> pls suggest me a better solution|||"Ashish Prasad" <ashishbitm@.gmail.com> wrote in message
news:97e9525a-73c6-4b5d-a39e-e0d872779f48@.d70g2000hsb.googlegroups.com...
> This is a small problem .. hope I could find quick answer :)
> I am trying to write a query which uses a subquery in its where
> condition. there are 2 columns (both PK) for which checking is
> required.
> I can run this query in DB2 with no problems. what is the correct
> approach to do that in SQL Server 2005 ?
> SELECT EMP_ID,DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> WHERE
> (EMP_ID,DEPT_ID)
> NOT IN
> (SELECT MANAGER_ID,MGR_DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> )
> Note : I did managed to do the same in SQL with combining the 2
> columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
> as index scan doesn't apply here.
> pls suggest me a better solution
Try one of the following. There is a difference however. Your query would
return no rows at all if either of MANAGER_ID or MGR_DEPT_ID contain any
nulls. Very often that is not a desirable result so the two alternatives
I've given here might be suitable.
SELECT EMP_ID,DEPT_ID
FROM STAGE_RMRS_PPLSFT
EXCEPT
SELECT MANAGER_ID, MGR_DEPT_ID
FROM STAGE_RMRS_PPLSFT;
SELECT EMP_ID,DEPT_ID
FROM STAGE_RMRS_PPLSFT S
WHERE NOT EXISTS
(SELECT MANAGER_ID,MGR_DEPT_ID
FROM STAGE_RMRS_PPLSFT
WHERE MANAGER_ID = S.EMP_ID
AND MGR_DEPT_ID = S.DEPT_ID);
Going by the column names alone I would guess this table is not well
normalized (MANAGER_ID->MGR_DEPT_ID?), which is something you should
probably consider fixing.
HTH
--
David Portas|||will use the correlated query with EXIST clause ..
Thanks a lot for your Help.. :)