Output based on group by and sum values

Question

Post reply

Output based on group by and sum values

DerbyNeal

Hall of Fame

Points: 3854
More actions
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,

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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