Help with update query statement

[Waynest]

Hi

Could someone suggest a query which will achieve the following:

I bcp a flat file into an import table. The flat file has multiple records for each product eg

SKU QTY

1234 1
1234 3
4321 1
1234 2


I need a query which will accrue the qtys for each sku and write the total per sku to another table ie

SKU QTY

1234 6
4321 1

The existing contents of the tables are discarded each time the file is imported so thats not a problem.

I'm quite new to SQL. Do I need a nested query or a correlated query or something else entirely to achieve this?

Thanks in advance...

 

[calahans]

This should work fine

SELET SKU,SUM(QTY)
FROM [YOUR TABLE NAME]
GROUP BY SKU

HTH

 

[Waynest]

I tried this but get a problem. Its a bit more fiddly than I originally let on - the sku consists of 7 independent fields which I cat into an alias. This is as far as I get before I hit the problem:

SELECT CrossOffsImport.PrdGrp + CrossOffsImport.StkCat + CONVERT(char(1),CrossOffsImport.grade) + CrossOffsImport.Brand + CrossOffsImport.style + CrossOffsImport.shade + CONVERT(char(1), CrossOffsImport.size) AS sku, SUM(CrossOffsImport.totprs) as qty
FROM CrossOffsImport
GROUP BY CrossOffsImport.sku

(grade and size are ints so I have to CONVERT to cat them)
I get the message "Invalid column name 'sku'". If I remove the SUM & replace GROUP BY with ORDER BY it executes. Bah. What am I doing wrong?

 

[ColinM]

I don't think you can use an alias in a group by function, which can be a bit of a pain.
But you can cut & paste to make this.

SELECT CrossOffsImport.PrdGrp + CrossOffsImport.StkCat + CONVERT(char(1),CrossOffsImport.grade) + CrossOffsImport.Brand + CrossOffsImport.style + CrossOffsImport.shade + CONVERT(char(1), CrossOffsImport.size) AS sku, SUM(CrossOffsImport.totprs) as qty
FROM CrossOffsImport
GROUP BY CrossOffsImport.PrdGrp + CrossOffsImport.StkCat + CONVERT(char(1),CrossOffsImport.grade) + CrossOffsImport.Brand + CrossOffsImport.style + CrossOffsImport.shade + CONVERT(char(1), CrossOffsImport.size)