?
Solved

T-SQL math help

Posted on 2006-11-28
6
Medium Priority
?
532 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
[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
  • Learn & ask questions
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 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 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

by:sammaell
ID: 18030836
thank you!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.
Suggested Courses

800 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