Solved

T-SQL math help

Posted on 2006-11-28
6
498 Views
Last Modified: 2012-06-27
one table, 2 columns. columnA has either "rtnsup" or "invsup" as the values,  columnB has amounts.

i can do select sum(columnB) as Total to get a total.

what i need is to total up columnB where columnA is invsup, total up columnB where columnA is rtnsup, and then take total of rtnsup from total of invsup.
0
Comment
Question by:sammaell
6 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18029562
SELECT ColumnA, SUM(ColumnB) as Tot
FROM urTble
GROUP BY ColumnA
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18029575
or

SELECT
 (SELECT SUM(ColumnB) FROM urTable  WHERE ColumnA = 'rtnsup' ) Sumrtnsup,
 (SELECT SUM(ColumnA) FROM urTable WHERE ColumnA = 'invsup' ) Suminvsup,
 SUM(ColumnB) as Total
FROM urTable
0
 
LVL 9

Assisted Solution

by:dduser
dduser earned 400 total points
ID: 18029580
Select Sum(Case When ColumnA = 'invsup' then ColumnB Else 0) as InvsupTotal,
Sum(Case When ColumnA = 'rtnsup' then ColumnB Else 0) as RtnsupTotal from YourTable

Regards,

dduser
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:sammaell
ID: 18030027
Select Sum(Case When ColumnA = 'invsup' then ColumnB Else 0) as InvsupTotal,
Sum(Case When ColumnA = 'rtnsup' then ColumnB Else 0) as RtnsupTotal from YourTable

im using:

select
sum(case when soptype = 'rtnsup' then xtndprce else 0) as YTDreturns
from sop30300

is giving me incorrect syntax near " ) " .  

where am i going wrong?
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 100 total points
ID: 18030630
Keyword "end" missing:

SELECT SUM(case when soptype = 'rtnsup' then xtndprce else 0 end) as YTDreturns,
    SUM(case when soptype = 'invsup' then xtndprce else 0 end) as YTDGross,
    SUM(case when soptype = 'invsup' then xtndprce else 0 end) -
    SUM(case when soptype = 'rtnsup' then xtndprce else 0 end) as YTDNet
from sop30300

0
 

Author Comment

by:sammaell
ID: 18030836
thank you!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now