display only one record with summed quantity of the ones that have similarites

Question

display only one record with summed quantity of the ones that have similarites

lcarrethers

SSCommitted

Points: 1917
More actions
June 18, 2010 at 7:53 am

#222998

CREATE TABLE test(
ITEM VARCHAR(30),
QUANTITY numeric(38,5)
)
INSERT INTO TEST
SELECT 'RMA2052', 8.00000 UNION ALL
SELECT 'RMA2052', 10.00000 UNION ALL
SELECT 'RMA2052D', 0.50000 UNION ALL
SELECT 'RMAP20102', 8.00000 UNION ALL
SELECT 'RMAP20102', 10.00000 UNION ALL
SELECT 'RMAP20102', 8.00000 UNION ALL
SELECT 'RMA0151', 10.00000 UNION ALL
SELECT 'RMA0151D', 0.25000
Example of what i would like to see
ITEM QUANTITY
rma2052 18.50000
RMAP20102 26.00000
RMA0151 10.25000

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

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

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

Your output contains three rows, each with an 'Item' which is a prefix of the Item values in your input table. The length of the prefix part is variable. Can you describe the rules which determine how many characters - reading from the left - you want to use for the 'Item' prefix in the output table?

^{“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

lcarrethers SSCommitted Points: 1917 More actions · Answer 2

This is what the output is of the sample code

Item Quanity

RMA2052 8.00000

RMA2052 10.00000

RMA2052D 0.50000

RMAP20102 8.00000

RMAP2010210.00000

RMAP201028.00000

RMA0151 10.00000

RMA0151D0.25000

But as you can see for an expample The RMA2052 has 3 and one them has a D at the end. I would like display RMA2052 without the D and the total of all 3 records

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Try:

SELECT GroupItem AS Item

,SUM(QUANTITY) AS QUANTITY

FROM (

SELECT LEFT(ITEM, ISNULL(NULLIF(PATINDEX('%[0-9][A-Z]%', ITEM),0),30)) AS GroupItem, QUANTITY

FROM TEST

) a

GROUP BY GroupItem

This will also work:

SELECT LEFT(ITEM, ISNULL(NULLIF(PATINDEX('%[0-9][A-Z]%', ITEM),0),30)) AS Item, SUM(QUANTITY) AS QUANTITY

FROM TEST

GROUP BY LEFT(ITEM, ISNULL(NULLIF(PATINDEX('%[0-9][A-Z]%', ITEM),0),30))

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

lcarrethers SSCommitted Points: 1917 More actions · Answer 4

lcarrethers

SSCommitted

Points: 1917

June 18, 2010 at 8:41 am

#1182174

Thanks that works perfect