Solved

T-SQL math help

Posted on 2006-11-28
6
488 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 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

20 Experts available now in Live!

Get 1:1 Help Now