Get 2 column result from 1 field

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

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

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

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

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

  • 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

  • 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

  • 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

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

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • πŸ™‚

    \m/ O_o \m/

    Thnaks!

  • 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

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

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