Post reply

ADDING VALUES IN MULTIPLE TABLE WITH THE SAME COLUMS AND ROWS

  • February 10, 2012 at 11:24 pm

    #250612

    I need to have a single table whose values are derived from adding the values in the 3 tables below (vells, vells2, vells3) I know there should be a better way to add values from all the tables into a single table without writing such a long query.

    TABLE NAME VELLS

    AU GLGL1MONTH PERIOD

    AU-12312JAN2011

    AU-32123JAN2011

    AU-33334JAN2011

    TABLE NAME VELLS2

    AU GLGL1 MONTH PERIOD

    AU-12312JAN2011

    AU-32123JAN2011

    AU-33334JAN2011

    TABLE NAME VELLS3

    AU GL GL1 MONTH PERIOD

    AU-12312JAN2011

    AU-32123JAN2011

    AU-33334JAN2011

    RESULT FROM MY CODE

    SELECT VELLS.AU,(VELLS.GL+VELLS2.GL+VELLS3.GL) GL,(VELLS.GL1+VELLS2.GL1+VELLS3.GL)GL1,VELLS.[MONTH],VELLS.PERIOD AS PERIOD

    FROM VELLS FULL JOIN VELLS2 ON VELLS.AU=VELLS2.AU

    FULL JOIN VELLS3 ON VELLS.AU=VELLS3.AU

    AUGLGL1MONTHPERIOD

    AU-12335JAN2011

    AU-32168JAN2011

    AU-333911JAN2011

    The tables above are just an example since I have to implement this on 30 tables with each table having over 100,000 rows.

  • February 11, 2012 at 3:43 am

    #1445878

    Would the UNION approach help?

    WITH cte AS

    (

    SELECT AU, GL ,GL1, [MONTH], PERIOD

    FROM VELLS

    UNION ALL

    SELECT AU, GL ,GL1, [MONTH], PERIOD

    FROM VELLS2

    UNION ALL

    SELECT AU, GL ,GL1, [MONTH], PERIOD

    FROM VELLS3

    )

    SELECT AU, SUM(GL) as GL ,SUM(GL1) AS GL1, [MONTH], PERIOD

    FROM cte

    GROUP BY AU, [MONTH], PERIOD

    As a side note: There are different opinions what a "long query" is... 😉

    Edit: Using reserved words (such as MONTH) as column names usually isn't a good idea. I'd use a different name for this column, at least for the target table.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • February 11, 2012 at 7:40 pm

    #1445981

    LutzM,

    The Union all approach did help Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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