April 26, 2016 at 3:14 pm
Hi All,
i am trying to get avg of datesdiff value for the below data. I may have 0 in the datesdiff column if there is no difference in the dates, but AVG should be of all the datesdiff i.e.; 0+2+3/3 =1 but i am getting two rows. Please execute below to see what i am saying. can you please help me in figuring out where i am doing wrong.
Declare @a Table
(
Type varchar(30),
SubType varchar(30),
dates date,
datesdiff int
)
Insert into @a
values('a','ab','2016-04-01',0)
Insert into @a
values('a','ab','2016-04-01',2)
Insert into @a
values('a','ab','2016-04-01',3)
select * From @a
Select Type,SubType,dates,AVG(datesdiff) avgvalue
from @a
where datesdiff>=0
group by Type,SubType,dates
Thanks in advance and let me know if i am not clear.
April 26, 2016 at 3:27 pm
i think it's integer division. SQL maintains the datatype, and the avg is truncated to a integer.
change the datatype of the column from int to decimal(19,4) and you get 2.5
Declare @a Table
(
Type varchar(30),
SubType varchar(30),
dates date,
datesdiff decimal(19,4)
)
Insert into @a
values('a','ab','2016-04-01',0)
Insert into @a
values('a','ab','2016-04-01',2)
Insert into @a
values('a','ab','2016-04-01',3)
select * From @a
Select Type,SubType,dates,AVG(datesdiff) avgvalue
from @a
where datesdiff>=0
group by Type,SubType,dates
Lowell
April 26, 2016 at 3:35 pm
Ya Decimal or integer is ok but what i want in the output is only one row with 1 in it , i.e AVG of all 3 rows. (0+2+3)/3 but currently i am getting 2 rows with 0.0000 and 2.50000
April 26, 2016 at 3:42 pm
Your data has a tab in it, so you are getting two rows: one with a tab, and one without. SQL will often ignore trailing spaces, but tabs are not spaces, so it does not ignore trailing tabs.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 26, 2016 at 3:46 pm
That's because you don't have a trailing space in that first value of the type column.
It's a horizontal tab, which makes that value not equal to the others.
You'll need to handle that to make it work as expected.
Cheers!
EDIT: Heh, as usual, if you go off and do other things while posting, someone beats you to it. +1 to Drew's point.
April 26, 2016 at 4:01 pm
Thank you I need to check with ltrim(rtrim(avg(datesdiff))) to get in one row. I will keep you posted .
April 27, 2016 at 8:29 am
dhanekulakalyan (4/26/2016)
Thank you I need to check with ltrim(rtrim(avg(datesdiff))) to get in one row. I will keep you posted .
LTRIM() will remove leading spaces, RTRIM() will remove trailing spaces. Again tabs are not spaces, so they will be unaffected by LTRIM()/RTRIM().
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 6, 2016 at 1:45 pm
This will do the job:
DECLARE @a AS TABLE (
[Type] varchar(30),
SubType varchar(30),
dates date,
datesdiff decimal(19,4)
);
INSERT INTO @a VALUES('a','ab','2016-04-01',0);
INSERT INTO @a VALUES('a','ab','2016-04-01',2);
INSERT INTO @a VALUES('a','ab','2016-04-01',3);
SELECT *
FROM @a;
SELECT REPLACE([Type], CHAR(9), '') AS [Type], SubType, dates,
AVG(datesdiff) AS avgvalue
FROM @a
WHERE datesdiff >= 0
GROUP BY REPLACE([Type], CHAR(9), ''), SubType, dates;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply