Post reply

Output based on group by and sum values

  • June 17, 2010 at 9:57 am

    #220200

    Hi,

    I have a sql 2005 table (Table_A) that contains the following values (with column headers)

    DEl_No, L_Amt, T_Amt, A_Name

    087NT1, 24000, 28699, Physic

    087NT2, 02500, 02500,

    087NT4, 01200, 01200,

    087NT9, 00999, 00999,

    093NT1, 21000, 23990, Physic

    093NTA, 02990, 02990,

    I need outputs to Table_B where A_Name = 'Physic' and the values are grouped by a substring of Del_No

    1. The Del_No field needs to be substringed as substring(Del_No,1,5)

    2. The T_Amt should remain the same

    3. The L_Amt needs to be the sum of the substring(Del_No,1,5) group. This value should be the same as the T_Amt (for example 24000 + 2500 + 1200 + 999 = 28699)

    Therefore I need the following outputs displayed in table_B:

    Del_No, L_Amt, T_Amt, A_Name

    087NT, 28699, 28699, Physic

    093NT, 23990, 23990, Physic

    Any ideas please?

    Thanks in advance,

  • June 17, 2010 at 10:18 am

    #1181695

    This should give you a few ideas

    DROP TABLE #TABLE_A

    CREATE TABLE #TABLE_A (DEl_No VARCHAR(6), L_Amt INT, T_Amt INT, A_Name VARCHAR(6))

    INSERT INTO #TABLE_A (DEl_No, L_Amt, T_Amt, A_Name)

    SELECT '087NT1', 24000, 28699, 'Physic' UNION ALL

    SELECT '087NT2', 02500, 02500, NULL UNION ALL

    SELECT '087NT4', 01200, 01200, NULL UNION ALL

    SELECT '087NT9', 00999, 00999, NULL UNION ALL

    SELECT '093NT1', 21000, 23990, 'Physic' UNION ALL

    SELECT '093NTA', 02990, 02990, NULL

    SELECT SuperGroup, L_Amt = SUM_L_Amt, T_Amt, A_Name

    FROM (

    SELECT SuperGroup = LEFT(DEl_No, 5),

    SUM_L_Amt = SUM(L_Amt) OVER (PARTITION BY LEFT(DEl_No, 5)),

    DEl_No, L_Amt, T_Amt, A_Name

    FROM #TABLE_A

    ) d

    WHERE SUM_L_Amt = T_Amt

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • June 17, 2010 at 10:24 am

    #1181698

    Here's another way

    SELECT LEFT(a.Del_No,5) AS Del_No,

    SUM(b.L_Amt) AS L_Amt,

    a.T_Amt,

    a.A_Name

    FROM Table_A a

    INNER JOIN Table_A b ON LEFT(b.Del_No,5)=LEFT(a.Del_No,5)

    WHERE a.A_Name= 'Physic'

    GROUP BY a.A_Name,LEFT(a.Del_No,5),a.T_Amt

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • June 17, 2010 at 10:31 am

    #1181702

    Many thanks guys. Perfect!

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

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