Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# T-SQL math help

Posted on 2006-11-28
Medium Priority
538 Views
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
Question by:sammaell
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 75

Expert Comment

ID: 18029562
SELECT ColumnA, SUM(ColumnB) as Tot
FROM urTble
GROUP BY ColumnA
0

LVL 75

Expert Comment

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

dduser earned 1600 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

Author Comment

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 70

Accepted Solution

Scott Pletcher earned 400 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

ID: 18030836
thank you!
0

## Featured Post

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that witâ€¦
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.
###### Suggested Courses
Course of the Month11 days, 10 hours left to enroll