CODEWITHSUNDEEP
sql
user3130669
user3130669

Reputation: 27

SQL command error on "AVG"

Trying to get average from, but keep receiving error message:

Operand data type varchar is invalid for avg operator.

Here's the SQL:

select 
        COUNT(Amount)AS "TOTAL Sales Daily",
        MIN(Amount) AS "MIN Sales Daily", 
        MAX(Amount) AS "MAX Sales Daily",
        CAST(AVG(Amount)AS INTEGER) AS "AVG Salses Daily"          
from CMP_SalesDaily_Data;

Upvotes: 0

Views: 2100

Answers (3)

Joe
Joe

Reputation: 63424

CAST is in the wrong place.

select 
        COUNT(Amount)AS "TOTAL Sales Daily",
        MIN(Amount) AS "MIN Sales Daily", 
        MAX(Amount) AS "MAX Sales Daily",
        AVG(CAST(Amount AS INTEGER)) AS "AVG Salses Daily"          
from CMP_SalesDaily_Data;

One way to handle this efficiently, if you have no control over AMOUNT's data type, might be to do it on the way in:

select 
        COUNT(Amount)AS "TOTAL Sales Daily",
        MIN(Amount) AS "MIN Sales Daily", 
        MAX(Amount) AS "MAX Sales Daily",
        AVG(Amount) AS "AVG Salses Daily"          
from (
  select cast(amount as integer) as amount from CMP_SalesDaily_Data
);

Still, remember to verify that the CAST isn't doing damage to the data in some fashion (such as you don't have things like "N/A" in the data that need to be handled cleanly).

Upvotes: 3

BWS
BWS

Reputation: 3836

try this:

AVG(cast(Amount AS INTEGER)) AS "AVG Salses Daily"

Upvotes: 0

Shiva
Shiva

Reputation: 20935

Your Amount field might be of type VARCHAR. Try CAST or CONVERT on it before passing it on to AVG.

(Assuming this is SQL-Server)

Upvotes: 0

Related Questions