Post reply

Help with Query

  • May 16, 2007 at 4:35 pm

    #117281

    I don't know how to call this kind of query but here is the situation.  Let say I have this table with data:

    create table #a (ID int, AuditDateTime datetime, colA int, colB int, colC int)

    insert #a  values (1, '2007-05-04 11:21:09.017', 10, null, null)

    insert #a  values (2, '2007-05-04 11:21:09.017', null, 100, null)

    insert #a  values (3, '2007-05-04 11:21:09.017', null, null, 1000)

    insert #a  values (4, '2007-05-04 15:58:28.770', 20, null, null)

    insert #a  values (5, '2007-05-04 15:58:28.770', null, 200, null)

    insert #a  values (6, '2007-05-04 15:58:28.770', null, null, 2000)

    I am trying to write a query to produce the result as below:

    AuditDateTime                   colA  colB  colC

    2007-05-04 11:21:09.017   10     100   1000

    2007-05-04 15:58:28.770   20     200   2000

    So far I can't get the wanted result.  Can you guys give me a hand? 

    Thanks.

  • May 16, 2007 at 4:43 pm

    #706958

    SELECT AuditDateTime,

        SUM(COALESCE(ColA,0)) AS colA,

        SUM(COALESCE(ColB,0)) AS colB,

        SUM(COALESCE(ColC,0)) AS colC

    FROM #a

    GROUP BY AuditDateTime

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • May 16, 2007 at 4:48 pm

    #706959

    ouch

    Thanks a lot!

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

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