In SQL, how to sum numerical values in one column based on string values in another column

Question

I'd like my procedure to return the total of AcceptedProducts by PlantName.

Could someone help me to correct my procedure?

CREATE PROCEDURE spAcceptedByCountry @Year INT, @Month INT
AS
    SELECT      CountryCode,
                CountryName,
                REPLACE(SUBSTRING([PlantName], CHARINDEX('-', [PlantName]), LEN([PlantName])), '-', '') AS [PlantName],
                SUM(AcceptedProducts) AS AcceptedProducts 
    FROM        vAcceptedByCountry
    WHERE       YEAR(DateOfManufacture) = @Year AND MONTH(DateOfManufacture) = @Month
    GROUP BY    CountryCode,
                CountryName,
                PlantName,
                AcceptedProducts
    ORDER BY    CountryCode;
GO

Thank you for your help. Kind Regards.

Remove AcceptedProducts from GROUP BY clause and replace PlantName with REPLACE(SUBSTRING([PlantName], CHARINDEX('-', [PlantName]), LEN([PlantName])), '-', '') — Dmitry Kolchev
– Dmitry Kolchev, Commented Sep 3, 2020 at 5:58
The question makes no sense. The question doesn't mention year and month, which are prominent arguments to the stored procedure. In fact, a stored procedure doesn't even make sense for this. A user-defined function would be more appropriate. — Gordon Linoff
– Gordon Linoff, Commented Sep 3, 2020 at 12:23

Athanasios Kataras · Accepted Answer · 2020-09-03 06:08:07Z

1

Try this:

CREATE PROCEDURE spAcceptedByCountry @Year INT, @Month INT
AS
    SELECT      CountryCode,
                CountryName,
                REPLACE(SUBSTRING([PlantName], CHARINDEX('-', [PlantName]), LEN([PlantName])), '-', '') AS [PlantName],
                SUM(AcceptedProducts) AS AcceptedProducts 
    FROM        vAcceptedByCountry
    WHERE       YEAR(DateOfManufacture) = @Year AND MONTH(DateOfManufacture) = @Month
    GROUP BY   1, 2, 3
    ORDER BY    CountryCode;
GO

You don't want to group by the aggregate you are trying to calculate.

So, now you are calculating the accepted products per countrycode/countryname/plantname, where the plant name is not the column but the item in your select clause.

edited Sep 3, 2020 at 6:08

answered Sep 3, 2020 at 5:55

Athanasios Kataras

26.7k6 gold badges40 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user14212810 Over a year ago

Thank you very much Athanasios. This worked perfectly. I truly appreciate your help. Thank you for the time you kindly spent on this.

Collectives™ on Stack Overflow

In SQL, how to sum numerical values in one column based on string values in another column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related