Professional Web Applications Themes

SUM() question - Microsoft SQL / MS SQL Server

I have the following table tbl1 Name Amount AddTogether Tom 40 0 Steve 10 1 Steve 15 1 Steve 20 1 Simon 25 0 Simon 30 1 Simon 35 1 Wayne 5 0 Wayne 10 0 I want to write a SELECT query that will return the following results: Name Amount Tom 40 Steve 45 Simon 25 Simon 65 Wayne 5 Wayne 10 The Amount for Tom is 40 because there is only one entry for Tom. The Amount for Steve is 45 because Steve is in the table 3 times and for each record AddTogether=1. So the SUM of ...

  1. #1

    Default SUM() question

    I have the following table

    tbl1

    Name Amount AddTogether
    Tom 40 0
    Steve 10 1
    Steve 15 1
    Steve 20 1
    Simon 25 0
    Simon 30 1
    Simon 35 1
    Wayne 5 0
    Wayne 10 0

    I want to write a SELECT query that will return the
    following results:

    Name Amount
    Tom 40
    Steve 45
    Simon 25
    Simon 65
    Wayne 5
    Wayne 10

    The Amount for Tom is 40 because there is only one entry
    for Tom.

    The Amount for Steve is 45 because Steve is in the table 3
    times and for each record AddTogether=1. So the SUM of 10,
    15, and 20 is 45.

    The Amount for Simon is 25 and 65 because the first amount
    for Simon is 25 and AddTogether=0. The second amount for
    Simon is 65 because the second and third amounts for Simon
    are 30 and 35 and both these records have AddTogether=1

    The Amounts for Wayne are 5 and 10 because both of the
    records for Wayne in the table have AddTogether=0.

    I would greatly appreciate any help. Thanks.

    Derek Ruesch

    Derek Guest

  2. #2

    Default Re: SUM() question

    Try:

    SELECT Name, COALESCE(c1, c2) AS "Amount"
    FROM ( SELECT Name,
    CASE AddTogether WHEN 0
    THEN Amount
    END,
    SUM(CASE AddTogether WHEN 1
    THEN Amount
    END)
    FROM tbl
    GROUP BY Name, CASE AddTogether WHEN 0
    THEN Amount
    END ) D (name, c1, c2 )
    ORDER BY Name, Amount ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  3. #3

    Default Re: SUM() question

    Please include DDL with future questions like this - it would help to know
    the primary key in this case.

    This query should give the result you want:

    SELECT [name], SUM(amount) AS amount
    FROM Sometable
    GROUP BY [name],
    CASE addtogether
    WHEN 1 THEN 0x0
    ELSE NEWID() END
    ORDER BY [name], amount

    If you have a single column primary key, put it in place of NEWID() above
    and in place of 0x0 put a value that cannot occur in your PK but is
    implicitly castable to your PK column datatype.

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



    David Guest

  4. #4

    Default Re: SUM() question

    Maybe we can keep it simple...
    if the order is not important.


    select name, sum(Amount)
    from temp
    where AddTogether=1
    group by name
    union
    select name, Amount
    from temp
    where AddTogether=0


    Drazen Guest

Similar Threads

  1. Newbie Question: Biz Card Template Question
    By Thomas_Porter@adobeforums.com in forum Adobe Indesign Windows
    Replies: 4
    Last Post: May 30th, 08:08 AM
  2. Replies: 9
    Last Post: April 27th, 04:44 AM
  3. Pen Tool Use Question. (Embarrassingly Newbie Question)
    By Bozo Schmozo in forum Macromedia Flash
    Replies: 0
    Last Post: November 12th, 10:00 PM
  4. regexp question + html::pr question on the side
    By boris in forum PERL Miscellaneous
    Replies: 4
    Last Post: September 27th, 02:24 AM
  5. newB question: related tables question
    By Blue man in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: June 30th, 04:13 AM

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