CODEWITHSUNDEEP
sqlsql-serveraggregate
hayes
hayes

Reputation: 59

Stored procedure to select values in table1, Group By, Sum, then insert those values into table2

I need a stored procedure to select rows from one table, do a Group By, Sum, and then insert the results into another table with their values.

I guess I'll have to do a merge when rfds_processing_id (similar to an order number) matches existing rows, and insert when it doesn't. I am currently doing something similar in Linq in my controller, but can't figure it out in SQL.

ALTER PROCEDURE [dbo].[ProposedSummary]
    (@rfds_processing_id UNIQUEIDENTIFIER = NULL)
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO Summary_Proposed(model, type, elevation, qty)
        SELECT model, type, elevation, SUM(qty)
        FROM Current_Antenna C
        WHERE C.rfds_processing_id = @rfds_processing_id
        GROUP BY C.model
END

This is what I'm doing in my Controller and sending to my View

var summary_c = current
    .Where(x => !string.IsNullOrWhiteSpace(x.model) && (x.rfds_processing_id == ))
    .GroupBy(x => x.model)
    .Select(x =>
    {
        var c = new
        {
            model = x.Key,
            type = x.Select(y => y.type).First(),
            elevation = x.Select(y => y.elevation).First(),
            qty = x.Sum(y => y.qty)
        };
        return c;
    }).ToList();

Here's the update for the first part that is working correctly

INSERT INTO Summary_Current(model, type, elevation, rfds_processing_id, qty)
    SELECT model, type, elevation, rfds_processing_id, SUM(qty)
    FROM Current_Antenna C
    WHERE C.rfds_processing_id = @rfds_processing_id 
      AND C.model != '' 
      AND C.type IS NOT NULL 
      AND C.elevation IS NOT NULL
GROUP BY C.model, C.type, C.elevation, rfds_processing_id

Upvotes: 2

Views: 148

Answers (2)

Soth Sopheak Trea
Soth Sopheak Trea

Reputation: 1

Better use try catch block for insert transaction

   begin transaction 
        INSERT INTO Summary_Proposed(model, type, elevation, qty)
        SELECT model, type, elevation, SUM(qty)
        FROM Current_Antenna C
        WHERE C.rfds_processing_id = @rfds_processing_id AND
        C.type IS NULL AND C.elevation IS NULL
        GROUP BY C.model, C.type, C.elevation;
   commit transaction 
 --Alert success message 
   end try  
   begin catch 
-- Alert error message 
   end catch

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

Your current proc looks OK to me, except that the GROUP BY clause should contain all non aggregate columns which also appear in the SELECT:

INSERT INTO Summary_Proposed(model, type, elevation, qty)
SELECT model, type, elevation, SUM(qty)
FROM Current_Antenna C
WHERE C.rfds_processing_id = @rfds_processing_id AND
      C.type IS NULL AND C.elevation IS NULL
GROUP BY C.model, C.type, C.elevation;

Upvotes: 6

Related Questions