Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2011-03-18
4
770 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:JohnJMA
4 Comments
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35168707
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

0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35169700
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

0
 
LVL 4

Accepted Solution

by:
samijsr earned 500 total points
ID: 35170498
As of your second Table Field5 and 6 are not Unique key, then in this case you have to manipulate the data of Field5 and 6 as you can not use simple Group by clause for all 6 fileds as It may casue duplicate the Field 1,2,3,4.
Now u did not mention data type of Field5 and 6 if it's numeric you can use sum() or any other function
other wise
Insert Into NewTable
Select distinct T1.Field1, T1.Field2, T1.Field3, T1.Field4,T2.Field5,T2.Field6,Field7sum,Field8Sum
(
Select Field1, Field2, Field3, Field4,  Sum(Field7) Field7Sum, Sum(Field8) Field8Sum
    From OrigTable  Group By Field1, Field2, Field3, Field4)T1
left inner Join
(Select Field1, Field2, Field3, Field4, Field5, Field6, Sum(Field7) , Sum(Field8)  
  From OrigTable  Group By Field1, Field2, Field3, Field4, Field5, Field6 )T2
on T1.Field1=T2.Field1 and T1.Field2=T2.Field2 and T1.Field3=T2.Field3 and T1.Field4=T2.Field4
0
 

Author Closing Comment

by:JohnJMA
ID: 35290347
I did get it to work wiht your example samijsr.  And Filed5 and 6 are not numeric.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Virtual SQL Server 2014 Standard 35 91
t-sql need help on t-sql 10 26
SQL Log size 3 17
How to use spatial data types in SQL Server Database project 2 24
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question