Get 2 column result from 1 field

Question

Get 2 column result from 1 field

davdam8

Hall of Fame

Points: 3620
More actions
June 13, 2013 at 11:50 am

#276340

Hi geniuses
Let say I got a table:
Years | Value
2000 | 2
2001 | 4
2002 | 5
2003 | 3
2004 | 2
I need to get the sum of the Value for all the years in 1 column and the sum(Value) to 2002 in another column.
in order to give:
Sumof.All | Sumto.2002
16 | 11
Thanks in advance!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Seems like a semi strange requirement but certainly not to bad. Notice how I posted consumable ddl and sample data? This is something you should do in the future.

Here are a couple of different ways...I am sure there are plenty of others.

create table #Something

(

Years int,

SomeValue int

)

insert #Something(Years, SomeValue)

Values

(2000, 2)

,(2001, 4)

,(2002, 5)

,(2003, 3)

,(2004, 2)

select SUM(SomeValue) as SumOfAll, (select SUM(SomeValue) from #Something where Years <= 2002) as SumTo2002

from #Something

select SUM(SomeValue) as SumOfAll, x.Sumto2002

from #Something

cross apply (select SUM(SomeValue) as Sumto2002 from #Something where Years <= 2002)x

group by x.Sumto2002

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

How about using a CASE statement to filter the years needed?

DECLARE @Something table

(

Years int,

SomeValue int

)

insert @Something(Years, SomeValue)

Values

(2000, 2)

,(2001, 4)

,(2002, 5)

,(2003, 3)

,(2004, 2)

SELECT SUM( SomeValue) SumOfAll, SUM( CASE WHEN Years <= 2002 THEN SomeValue END) Sumto2002

FROM @Something

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Luis Cazares (6/13/2013)
How about using a CASE statement to filter the year needed?
DECLARE @Something table
(
Years int,
SomeValue int
)
insert @Something(Years, SomeValue)
Values
(2000, 2)
,(2001, 4)
,(2002, 5)
,(2003, 3)
,(2004, 2)
SELECT SUM( SomeValue) SumOfAll, SUM( CASE WHEN Years <= 2002 THEN SomeValue END) Sumto2002
FROM @Something

There you go again Luis...taking a perfectly slow version and making it faster. 😀

Not really sure why my brain was stuck doing it the hard way on this one. Thanks!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

davdam8 Hall of Fame Points: 3620 More actions · Answer 4

There an issue:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How do I work around this?

Thanks!

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 5

Can you post the query you are using. That might give us some idea.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

davdam8 Hall of Fame Points: 3620 More actions · Answer 6

SELECT

ID,

CASE WHEN (SUM([VALPAYREAL]) IS NULL) THEN 0 ELSE SUM([VALPAYREAL]) END AS PAYREAL,

CASE WHEN (SUM([VALPAYPLAN]) IS NULL) THEN 0 ELSE SUM([VALPAYPLAN]) END AS PAYPLAN,

(SELECT SUM([VALPAYPLAN])

FROM ABC

WHERE DATE <= Getdate() AND ([TYPE] = 'PAYMENT') GROUP BY ID )

AS PAYPLANTODATE,

CASE WHEN (SUM([VALPAYPROCESS])IS NULL) THEN 0 ELSE SUM([VALPAYPROCESS]) END AS PAYPROCESS

FROM ABC

WHERE ([TYPE] = 'PAYMENT')

GROUP BY ID

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 7

This is the problem

(SELECT SUM([VALPAYPLAN])

FROM ABC

WHERE DATE <= Getdate() AND ([TYPE] = 'PAYMENT') GROUP BY ID )

You need to add an additional filter to this sub query

SELECT

ID

, CASE

WHEN (SUM([VALPAYREAL]) IS NULL) THEN 0

ELSE SUM([VALPAYREAL])

END AS PAYREAL

, CASE

WHEN (SUM([VALPAYPLAN]) IS NULL) THEN 0

ELSE SUM([VALPAYPLAN])

END AS PAYPLAN

, (SELECT SUM([VALPAYPLAN])

FROM ABC

WHERE

DATE <= Getdate()

AND ([TYPE] = 'PAYMENT')

AND ID = OUTERABC.ID

GROUP BY

ID ) AS PAYPLANTODATE

, CASE

WHEN (SUM([VALPAYPROCESS])IS NULL) THEN 0

ELSE SUM([VALPAYPROCESS])

END AS PAYPROCESS

FROM

ABC AS OUTERABC

WHERE

([TYPE] = 'PAYMENT')

GROUP BY ID

Edit : Code reformated, as Chris made me feel guilty about not doing it in the frist place 😀

_________________________________________________________________________
SSC Guide to Posting and Best Practices

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Hands up if you find this easier to scan:

SELECT

o.ID,

PAYREAL = ISNULL(SUM(o.VALPAYREAL),0),

PAYPLAN = ISNULL(SUM(o.VALPAYPLAN),0),

x.PAYPLANTODATE,

PAYPROCESS = ISNULL(SUM(o.VALPAYPROCESS),0)

FROM ABC o

CROSS APPLY (

SELECT

PAYPLANTODATE = SUM(i.VALPAYPLAN)

FROM ABC i

WHERE i.[DATE] <= GETDATE()

AND i.[TYPE] = 'PAYMENT'

AND i.ID = o.ID

GROUP BY i.ID),

) x

WHERE o.[TYPE] = 'PAYMENT'

GROUP BY o.ID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 9

+1. Definately easier to read Chris, Formatting FTW.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

davdam8 Hall of Fame Points: 3620 More actions · Answer 10

davdam8

Hall of Fame

Points: 3620

June 14, 2013 at 4:38 am

#1624030

🙂

\m/ O_o \m/

Thnaks!

barry.mcconnell Default port Points: 1447 More actions · Answer 11

Another approach using CTEs

create table #Table1

(

Years int,

[Value] int

)

insert #Table1(Years, [Value])

Values

(2000, 2)

,(2001, 4)

,(2002, 5)

,(2003, 3)

,(2004, 2)

GO

with SumAll(FullTotal) AS

(SELECT SUM([value]) FROM #Table1)

,SumPartial(PartialTotal) AS

(SELECT SUM([Value]) FROM #Table1 WHERE Years <= 2002)

SELECT a.FullTotal, b.PartialTotal

FROM SumALL a, SumPartial b

There are no facts, only interpretations.
Friedrich Nietzsche