Post reply

Update using FROM and SUM: Need to calculate percentage

  • June 9, 2009 at 1:51 pm

    #131738

    I am building a report to be rendered in a ASP.Net datagrid. I need to update a temp table. Something like:

    UPDATE #t_q

    SET p_top= PC

    FROM #t_q

    INNER JOIN (SELECT CONVERT(DECIMAL(5,2), Sum(Is_Correct)*100.0/Count(*)) AS PC

    FROM analysis_1 where userName IN (SELECT LoginName from #t_aq where t_ype = 'T')

    Group By qID

    )

    This basically gets % data. I get Incorrect syntax ')' on the last close parenthesis.

    The temp tables are:

    CREATE TABLE #t_aq(

    s_login char(50),

    s_value float,

    t_ype char(1),

    ID varchar(100)

    )

    CREATE TABLE #t_q(

    q_id varchar(255),

    p_high Decimal(5,2),

    p_low Decimal(5,2)

    )

    The analysis_1 has userid and other info. Table #t_aq gets populated from analysis_1.This has the ID also.

  • June 9, 2009 at 2:11 pm

    #1007043

    What a see from brief glance is that you are trying to update a column that doesn't exist. p_top is not a column in #t_q table. Loginname is not a column in #t_aq table. You also do not have a join condition to join #t_q to the derived table

    also provide sample data in a readily consumable format. refer to link in my signature tips on posting code.

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

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

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