Professional Web Applications Themes

SUM - Microsoft SQL / MS SQL Server

I have two result sets that look like: Resultset 1 ------------------ GBP 10 USD 20 DEM 20 Resultset 2 ----------------- GBP 10 USD 40 I need a final result set Final Resultset ----------------- GBP 20 USD 60 DEM 20 How can achieve this ?...

  1. #1

    Default SUM

    I have two result sets that look like:

    Resultset 1
    ------------------
    GBP 10
    USD 20
    DEM 20


    Resultset 2
    -----------------
    GBP 10
    USD 40

    I need a final result set

    Final Resultset
    -----------------
    GBP 20
    USD 60
    DEM 20

    How can achieve this ?


    Nice Chap Guest

  2. #2

    Default Re: SUM

    select currency, sum(val) from
    (select currency, val from resultset1
    union all
    select currency, val from resultset2) A
    group by currency

    --
    -Vishal
    "Nice Chap" <ChapThunderbolt.com> wrote in message
    news:#IHHFchQDHA.304tk2msftngp13.phx.gbl...
    > I have two result sets that look like:
    >
    > Resultset 1
    > ------------------
    > GBP 10
    > USD 20
    > DEM 20
    >
    >
    > Resultset 2
    > -----------------
    > GBP 10
    > USD 40
    >
    > I need a final result set
    >
    > Final Resultset
    > -----------------
    > GBP 20
    > USD 60
    > DEM 20
    >
    > How can achieve this ?
    >
    >

    Vishal Parkar Guest

  3. #3

    Default Re: SUM

    SELECT col1, SUM(col2)
    FROM
    (SELECT col1, col2
    FROM Table1
    UNION ALL
    SELECT col1, col2
    FROM Table2) X
    GROUP BY col1

    (untested)

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  4. #4

    Default sum

    Trying to obtain total number of books per class along with other fields.

    classA 1 fname1 8 ...
    classA 2 fname2 3 ...
    classA 3 fname3 4 ...


    How to modify the query in below to return all fields and sum number
    of books also?.

    select *
    #tempbook
    group by class,order,name,Books,...

    should get total of 15 books in this case.





    --
    Sent by 3 from yahoo part from com
    This is a spam protected message. Please answer with reference header.
    Posted via http://www.usenet-replayer.com/cgi/content/new
    alexqa2003@yahoo.com Guest

  5. #5

    Default Re: sum

    One of the limitations of aggregate functions is that the more columns
    you choose to display, the more you break down your aggregate columns
    into smaller subtotals. If what you are trying to accomplish is truly a
    subtotal by class then try this...

    select class,sum(books)
    group by class


    If you need to keep all of the data and still keep the grand totals then
    try ...

    select * from
    (select class,sum(books) as gtotal
    from #tmpBook
    group by class) a
    inner join b ON a.class=b.class
    (select * from #tmpBook) b


    HTH,

    Robert

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Robert Guest

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139