SQL Server Performance

sum() insert question

Discussion in 'SQL Server 2008 General Developer Questions' started by postonoh, Apr 12, 2010.

  1. postonoh New Member

    is there a way to calculate the numbers in one table and insert the answers in an other table.
  2. Madhivanan Moderator

    [quote user="postonoh"]
    is there a way to calculate the numbers in one table and insert the answers in an other table.
    [/quote]
    Post some sample data with expected result
  3. Adriaan New Member

    Derived results can simply be displayed through an aggregate (GROUP BY) query, like
    SELECT SUM(t1.ValueColumn), t1.GroupingColumn
    FROM MyTable t1
    GROUP BY t1.GroupingColumn
    Inserting the results into another table is not really necessary, since you can always run the above query to get the current aggregate results.
    ... but if you insist:
    CREATE TABLE dbo.tblAggregateResults (SumValue INT, GroupValue VARCHAR(100))
    INSERT INTO dbo.tblAggregateResults (SumValue, GroupValue)
    SELECT SUM(t1.ValueColumn), t1.GroupingColumn
    FROM MyTable t1
    GROUP BY t1.GroupingColumn
    This is only for adding new results. If you look into updating results for an existing GroupValue, you will soon realize it's a lot easier to just run an aggregate query to get the current derived values.
  4. postonoh New Member

    this work excellent [:D] How would I update
    Here is my tables and columns
    table 1 = [dbo].[Narrative](
    [Nid] [int] IDENTITY(1,1) NOT NULL,
    [Project_ID] [int] NOT NULL,
    [Location] [nvarchar](50) NULL,
    [ContructionManager] [nvarchar](50) NULL,
    [Date] [smalldatetime] NULL,
    [MonitorSummary] [nvarchar](3000) NULL,
    [MIM] [numeric](18, 0) NULL,
    [MIF] [numeric](18, 0) NULL,
    [MAM] [numeric](18, 0) NULL,
    [MAF] [numeric](18, 0) NULL,

    Table 2 = [dbo].[Manpower](
    [Mid] [int] IDENTITY(1,1) NOT NULL,
    [BidPackage_ID] [int] NULL,
    [Project_ID] [int] NULL,
    [Date] [smalldatetime] NULL,
    [Female_Employees] [numeric](18, 0) NULL,
    [Minority_Employees] [numeric](18, 0) NULL,
    [Cleveland_Residents] [numeric](18, 0) NULL,
    [Total_Employees] [numeric](18, 0) NULL,
    With the query I am able insert the sum. Now I need to also insert the date corrensponding Project_ID
  5. paresh_sqldba New Member

    insert into table2
    select count(*) from table1

Share This Page