CODEWITHSUNDEEP
sqlsql-server
Ethan Edmondson
Ethan Edmondson

Reputation: 25

Invalid Column Name When Setting New Variable Equal to Expression

I need to calculate a percentage based off of two columns in my query and store that number in a new variable. When I set the variable equal to the expression I get an "Invalid column name" error.

SELECT
Count(Case When HMC_Place_Position is null Then 0 end) as Parts,
COUNT(*) AS Total_Parts,                                                                        /*Total Parts */

COUNT(CASE
    WHEN Outfeed_Place_Time IS NOT NULL THEN 1      
END) AS Total_Good_Parts                                                                        /*Total Good Parts */

FROM PartData_GKN05_C
WHERE Infeed_Pick_Time >= DATEADD(day,-7, GETDATE()) 
ALTER TABLE  PartData_GKN05_C Add Total_Good_Parts int
DECLARE @Total_Good_Percent AS float = ((Total_Good_Parts / Total_Parts)*100)

Upvotes: 2

Views: 496

Answers (2)

Johnny51714
Johnny51714

Reputation: 76

I believe you are looking to get the percentage of "good" parts from the total count of part recordss in PartData_GKN05_C.

So using your definition of " good parts " (Outfeed_Place_Time IS NOT NULL), the query below counts those and then divides by the count of all the part records in the table.

Since "count()" returns an integer value, we have to cast one of the "count()" as float BEFORE we divide them so that SQL does not return a zero (Since an "int / int" does not return decimals while a float does).

We only have to convert one of the "Count()" because SQL will implicitly convert the other one but feel free to convert both to float explicitly if you'd like.

Here is the code :

DECLARE 
    @Total_Good_Percent float

SELECT
    @Total_Good_Percent =   (
                                COUNT(
                                    CASE
                                        WHEN Outfeed_Place_Time IS NOT NULL
                                            THEN 1
                                        END
                                )
                                /convert(float,count(*))
                            )*100
FROM 
    PartData_GKN05_C
WHERE 
    Infeed_Pick_Time >= DATEADD(day, -7, GETDATE());

select
    @Total_Good_Percent

Upvotes: 1

Hasan Mahmood
Hasan Mahmood

Reputation: 978

try this code:

DECLARE @Total_Good_Parts INT, @Total_Parts INT;
SELECT
--Count(Case When HMC_Place_Position is null Then 0 end) as Parts,
@Total_Parts = COUNT(*), 
@Total_Good_Parts = COUNT(CASE
                              WHEN Outfeed_Place_Time IS NOT NULL
                              THEN 1
                          END)
FROM PartData_GKN05_C
WHERE Infeed_Pick_Time >= DATEADD(day, -7, GETDATE());

ALTER TABLE PartData_GKN05_C
ADD Total_Good_Parts INT;

DECLARE @Total_Good_Percent AS FLOAT= ((@Total_Good_Parts / @Total_Parts) * 100.00);

Upvotes: 0

Related Questions