Solved

T-SQL math help

Posted on 2006-11-28
6
506 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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:
Scott Pletcher 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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…

816 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

10 Experts available now in Live!

Get 1:1 Help Now