Post reply

Perform a count on a unique ID and sum two columns

  • August 21, 2012 at 12:53 am

    #266218

    Hi,

    Here is some sample Data

    CREATE TABLE #TESTING (acc_no varchar(20), number INT, c_name varchar(20), R_Value decimal(10,2), time_spent decimal(10,2) )

    INSERT INTO #TESTING VALUES ('C1232' ,4445, 'Tom', 345.7, 43.56)

    INSERT INTO #TESTING VALUES ('C1232' ,3456, 'Tom', 3454.7, 553.556)

    INSERT INTO #TESTING VALUES ('C1232',6789, 'Thomas', 1345.7, 463.556)

    INSERT INTO #TESTING VALUES ('C125632',1234, 'Will', 423.64, 233.77)

    INSERT INTO #TESTING VALUES ('C125632',2345, 'William', 56.76, 77.89)

    INSERT INTO #TESTING VALUES ('C125632',1345, 'Will', 444.56, 234.54)

    INSERT INTO #TESTING VALUES ('C125632',12344, 'Will', 34.27, 112.56)

    select * from #TESTING

    I basically need to perform a count on acc_no and sum R_Value and time_spent. I can drop column number. The column c_name may have similar names like Tom, Thomas etc. I only require one result ie c_name to be returned per acc_no and it doesn't matter which one.

    Here is the result set I would like

    CREATE TABLE #Result1 (acc_no varchar(20), count INT, c_name varchar(20), R_Value decimal(10,2), time_spent decimal(10,2) )

    INSERT INTO #Result1 VALUES ('C1232' ,3, 'Tom', 5146.1, 1060.672)

    INSERT INTO #Result1 VALUES ('C125632' ,4, 'Will', 959.23, 658.76)

    select * from #Result1

    I'm using a weird SQL called interactive SQL. Row_Number() doesnt work but dense_rank() does just in case you use an analytical function in your solution.

    I tried the following code but it returns multiple records.

    elect acc_no, count(acc_no) as count, sum(R_Value ) R_Value_1, sum(time_spent ) time_spent_1

    from #TESTING

    group by acc_no, R_Value , time_spent

    Thanks a million!!!

  • August 21, 2012 at 1:02 am

    #1527953

    Will this work for you?

    SELECT acc_no, [count]=COUNT(number), c_name=MAX(c_name)

    , R_Value=SUM(R_Value), time_spent=SUM(time_spent)

    FROM #TESTING

    GROUP BY acc_no

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • August 21, 2012 at 1:51 am

    #1527972

    Thanks so much!!! Appreciate the help!!!

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

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