SQL Avg function returning incorrect values

Santhi Dhanuskodi 1 Reputation point
2022-01-19T07:46:27.5+00:00

Avg function retuning wrong values,

below is my query

/* Avg Stock In */
select sum(decQtyIn)/31 as pcs_stockin,avg(decQtyIn) as pcs_stockin,
sum(hls_in)/31 as hls_stockin,avg(hls_in) as hls_stockin,
sum(ctn_crate_in)/31 as ctn_stockin,avg(ctn_crate_in) as ctn_stockin
from [VW_Fact_Inventory_All]
where year(cast(cast (skDateTransaction as varchar(10)) as date)) = 2021 and month(cast(cast (skDateTransaction as varchar(10)) as date)) = 10
group by year(cast(cast (skDateTransaction as varchar(10)) as date)), month(cast(cast (skDateTransaction as varchar(10)) as date))

sum/31 and avg - returning 2 different values. Ideally they should return same values. Please tell me why avg is not working.

166200-image.png
166226-sql-avg.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,125 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,586 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Olaf Helper 42,576 Reputation points
    2022-01-19T07:58:42.217+00:00

    sum/31 and avg - returning 2 different values

    Why do you divide by 31, because october do have 31 days? Do you have always only one record per day and this for all 31 days in october 2021?

    Please post table design as DDL, some sample data as DML statement and the expected result.


  2. LiHong-MSFT 10,046 Reputation points
    2022-01-19T08:52:11.573+00:00

    Hi,@Santhi Dhanuskodi
    Please check the data in [VW_Fact_Inventory_All]

    select *  
    from [VW_Fact_Inventory_All]  
    where year(cast(cast (skDateTransaction as varchar(10)) as date)) = 2021 and month(cast(cast (skDateTransaction as varchar(10)) as date)) = 10  
    

    And I guess there are far more than 31 rows in 2021.October.

    select sum(decQtyIn)/31 as pcs_stockin,avg(decQtyIn) as pcs_stockin,
    they should return same values

    They only return same values when there are exactly 31 rows of data in October

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments