asked on

MSSQL Insert into Table from another Table that some fields are SUM()

Hello,

I have a table, OrigTable, with 8 fields in it Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8. I want to Sum() Field7 and Filed8 using the Group By Field1, Field2, Field3, Field4. Then insert all 8 Fields into NewTable as Field1, Field2, Field3, Field4, Field5, Field6, Field7 (sum value), Field8 (sum value). The Unique key in NewTable is Field1, Field2, Field3, Field4. Field5 adn Field6 could have different values but it doesn't matter which value is used the field to insert the record in NewTable. Is this possible?

Thanks.

wdosanjos

It should be something like this: (you also need to group by Field5 and Field6)

Select Field1, Field2, Field3, Field4, Field5, Field6, Sum(Field7) Field7Sum, Sum(Field8) Field8Sum
  Into NewTable
  From OrigTable
 Group By Field1, Field2, Field3, Field4, Field5, Field6

Open in new window

JoeNuvo

problem is, if you want Field1 - 4 as "unique key" in NewTable
then you must do something with Field5, Field6 as well.
for ex. pick one of the value

INSERT INTO NewTable
SELECT Field1, Field2, Field3, Field4, MIN(Field5) AS Field5, MIN(Field6) AS Field6, SUM(Field7) AS Field7, SUM(Field8) AS Field8
FROM OrigTable
GROUP BY Field1, Field2, Field3, Field4

Open in new window

ASKER CERTIFIED SOLUTION

samijsr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JohnJMA

ASKER

I did get it to work wiht your example samijsr. And Filed5 and 6 are not numeric.