Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Get 2 column result from 1 field Expand / Collapse
Author
Message
Posted Thursday, June 13, 2013 11:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:56 AM
Points: 178, Visits: 668
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!
Post #1463233
Posted Thursday, June 13, 2013 12:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 13,069, Visits: 11,908
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1463240
Posted Thursday, June 13, 2013 12:25 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:25 PM
Points: 3,333, Visits: 7,193
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1463252
Posted Thursday, June 13, 2013 12:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 13,069, Visits: 11,908
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1463254
Posted Friday, June 14, 2013 3:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:56 AM
Points: 178, Visits: 668

There an issue:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , &gt;, &gt;= or when the subquery is used as an expression.

How do I work around this?

Thanks!
Post #1463443
Posted Friday, June 14, 2013 3:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:33 AM
Points: 2,616, Visits: 4,720
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/
Post #1463449
Posted Friday, June 14, 2013 4:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:56 AM
Points: 178, Visits: 668
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

Post #1463460
Posted Friday, June 14, 2013 4:10 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1463462
Posted Friday, June 14, 2013 4:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 7,120, Visits: 13,491
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1463466
Posted Friday, June 14, 2013 4:30 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
+1. Definately easier to read Chris, Formatting FTW.



_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1463467
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse