• Status: Solved
• Priority: Medium
• Security: Public
• Views: 100

# sql calculate averages

I need to calculate averages miles driven for the car on the day of the week and the overall average. The result need to be displayed on one line. I am using PIVOT table but seems no able to get it to work

``````			SELECT [Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]
FROM (
select CD.carkey,CD.startmileage,CD.endmileage,(CD.endmileage - CD.startmileage) milesDriven,DATENAME(WEEKDAY,SU.sessionStart) _WEEKDAY
from car_details CD
inner join Car C on C.carKey = CD.carKey
inner join SessionUnit SU on SU.sessionKey = CD.sessionKey
WHERE CD.carKey = 129
and SU.sessionStart BETWEEN '11/1/2016' AND '12/1/2016'
)AS T
PIVOT (
avg(T.milesDriven) FOR _weekDay IN ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])
) as piv
``````
0
erikTsomik
• 9
• 6
• 2
• +1
1 Solution

Database ExpertCommented:
Are you getting any error or the calculation is coming incorrect ?
0

Technology ScientistCommented:
In your SELECT clause you need a non-pivot field to hold your row of aggregate results;
try:

``````SELECT AvgMilesDriven, [Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]
FROM (
select CD.carkey,CD.startmileage,CD.endmileage,(CD.endmileage - CD.startmileage) milesDriven,DATENAME(WEEKDAY,SU.sessionStart) _WEEKDAY
from car_details CD
inner join Car C on C.carKey = CD.carKey
inner join SessionUnit SU on SU.sessionKey = CD.sessionKey
WHERE CD.carKey = 129
and SU.sessionStart BETWEEN '11/1/2016' AND '12/1/2016'
)AS T
PIVOT (
avg(T.milesDriven) FOR _weekDay IN ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])
) as piv
``````
0

Database ExpertCommented:
Try..

``````			SELECT milesDriven,[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]
FROM (
select CD.carkey,CD.startmileage,CD.endmileage,(CD.endmileage - CD.startmileage) milesDriven,DATENAME(WEEKDAY,SU.sessionStart) _WEEKDAY
from car_details CD
inner join Car C on C.carKey = CD.carKey
inner join SessionUnit SU on SU.sessionKey = CD.sessionKey
WHERE CD.carKey = 129
and SU.sessionStart BETWEEN '11/1/2016' AND '12/1/2016'
)AS T
PIVOT (
avg(T.milesDriven) FOR _weekDay IN ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])
) as piv
``````
0

System Architect, CF programmer Author Commented:
on this line SELECT milesDriven, getting the error message Invalid column name 'milesDriven'.
0

Database ExpertCommented:
Can you provide us some data to try ?
0

Database ExpertCommented:
Can you give us output of below-
``````SELECT DISTINCT carkey, SUM(milesDriven) OVER() TotalMilesDriven, SUM(milesDriven) OVER (PARTITION BY _WEEKDAY) MilesPerCarWeekDay
FROM
(
select CD.carkey,(CD.endmileage - CD.startmileage) milesDriven,DATENAME(WEEKDAY,SU.sessionStart) _WEEKDAY
from car_details CD
inner join Car C on C.carKey = CD.carKey
inner join SessionUnit SU on SU.sessionKey = CD.sessionKey
WHERE CD.carKey = 129
and SU.sessionStart BETWEEN '11/1/2016' AND '12/1/2016'
)AS T
``````
0

System Architect, CF programmer Author Commented:
Here is some data

``````carkey	startmileage	endmileage	milesDriven	_WEEKDAY
129	29671	29676	5	Sunday
129	29676	29681	5	Sunday
129	29690	29700	10	Monday
129	29700	29717	17	Monday
129	29720	29740	20	Sunday
129	29740	29750	10	Sunday
129	29750	29757	7	Saturday
129	29757	29761	4	Saturday
129	29761	29775	14	Sunday
129	29775	29791	16	Sunday
129	29775	29785	10	Sunday
129	29814	29840	26	Tuesday
129	29840	29855	15	Saturday
129	29855	29869	14	Saturday
129	29870	29874	4	Sunday
129	29874	29880	6	Sunday
``````
0

System Architect, CF programmer Author Commented:
``````carkey	TotalMilesDriven	MilesPerCarWeekDay
129	183	26
129	183	27
129	183	40
129	183	90
``````
0

Database ExpertCommented:
With this

``````SELECT DISTINCT carkey, _WEEKDAY , SUM(milesDriven) OVER() TotalMilesDriven, SUM(milesDriven) OVER (PARTITION BY _WEEKDAY) MilesPerCarWeekDay
FROM
(
select CD.carkey,(CD.endmileage - CD.startmileage) milesDriven,DATENAME(WEEKDAY,SU.sessionStart) _WEEKDAY
from car_details CD
inner join Car C on C.carKey = CD.carKey
inner join SessionUnit SU on SU.sessionKey = CD.sessionKey
WHERE CD.carKey = 129
and SU.sessionStart BETWEEN '11/1/2016' AND '12/1/2016'
)AS T
``````
0

System Architect, CF programmer Author Commented:
``````carkey	_WEEKDAY	TotalMilesDriven	MilesPerCarWeekDay
129	Monday	183	27
129	Saturday	183	40
129	Sunday	183	90
129	Tuesday	183	26
``````
0

Database ExpertCommented:
Try..

``````CREATE TABLE Cars
(
carkey	INT
,startmileage	 INT
,endmileage	INT
,milesDriven	 INT
,_WEEKDAY VARCHAR(50)
)
GO

INSERT INTO Cars VALUES
(129	,29671	,29676	,5	,'Sunday'),
(129	,29676	,29681	,5	,'Sunday'),
(129	,29690	,29700	,10	,'Monday'),
(129	,29700	,29717	,17	,'Monday'),
(129	,29720	,29740	,20	,'Sunday'),
(129	,29740	,29750	,10	,'Sunday'),
(129	,29750	,29757	,7	,'Saturday'),
(129	,29757	,29761	,4	,'Saturday'),
(129	,29761	,29775	,14	,'Sunday'),
(129	,29775	,29791	,16	,'Sunday'),
(129	,29775	,29785	,10	,'Sunday'),
(129	,29814	,29840	,26	,'Tuesday'),
(129	,29840	,29855	,15	,'Saturday'),
(129	,29855	,29869	,14	,'Saturday'),
(129	,29870	,29874	,4	,'Sunday'),
(129	,29874	,29880	,6	,'Sunday')

SELECT carkey , TotalMilesDriven , ISNULL([Monday],0) [Monday] ,
ISNULL([Tuesday],0)[Tuesday],ISNULL([Wednesday],0)[Wednesday],ISNULL([Thursday],0)[Thursday],ISNULL([Friday],0)[Friday]
,ISNULL([Saturday],0)[Saturday],ISNULL([Sunday],0) [Sunday] FROM
(
SELECT DISTINCT carkey , _WEEKDAY, SUM(milesDriven) OVER() TotalMilesDriven, SUM(milesDriven) OVER (PARTITION BY _WEEKDAY) MilesPerCarWeekDay
FROM
(
select CD.carkey,(CD.endmileage - CD.startmileage) milesDriven, _WEEKDAY
from Cars CD
)AS T
)k
PIVOT ( MAX(MilesPerCarWeekDay) FOR _WEEKDAY IN ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]) )y1
``````

Output

``````carkey      TotalMilesDriven Monday      Tuesday     Wednesday   Thursday    Friday      Saturday    Sunday
----------- ---------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
129         183              27          26          0           0           0           40          90

(1 row(s) affected)

(1 row(s) affected)

--
``````

Hope it helps !!
0

Database ExpertCommented:
I shall wait for some time if any changes are reqd.
0

System Architect, CF programmer Author Commented:
Well I need average not the totals
0

System Architect, CF programmer Author Commented:
what about my query without using a temp table
0

Database ExpertCommented:
Try..

``````SELECT carkey , TotalMilesDriven , ISNULL([Monday],0) [Monday] ,
ISNULL([Tuesday],0)[Tuesday],ISNULL([Wednesday],0)[Wednesday],ISNULL([Thursday],0)[Thursday],ISNULL([Friday],0)[Friday]
,ISNULL([Saturday],0)[Saturday],ISNULL([Sunday],0) [Sunday] FROM
(
SELECT DISTINCT carkey , _WEEKDAY, AVG(milesDriven) OVER() TotalMilesDriven, AVG(milesDriven) OVER (PARTITION BY _WEEKDAY) MilesPerCarWeekDay
FROM
(
select CD.carkey,(CD.endmileage - CD.startmileage) milesDriven, _WEEKDAY
from Cars CD
)AS T
)k
PIVOT ( MAX(MilesPerCarWeekDay) FOR _WEEKDAY IN ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]) )y1
``````
0

Database ExpertCommented:
``````SELECT carkey , TotalMilesDriven , ISNULL([Monday],0) [Monday] ,
ISNULL([Tuesday],0)[Tuesday],ISNULL([Wednesday],0)[Wednesday],ISNULL([Thursday],0)[Thursday],ISNULL([Friday],0)[Friday]
,ISNULL([Saturday],0)[Saturday],ISNULL([Sunday],0) [Sunday] FROM
(
SELECT DISTINCT carkey, _WEEKDAY , AVG(milesDriven) OVER() TotalMilesDriven, AVG(milesDriven) OVER (PARTITION BY _WEEKDAY) MilesPerCarWeekDay
FROM
(
select CD.carkey,(CD.endmileage - CD.startmileage) milesDriven,DATENAME(WEEKDAY,SU.sessionStart) _WEEKDAY
from car_details CD
inner join Car C on C.carKey = CD.carKey
inner join SessionUnit SU on SU.sessionKey = CD.sessionKey
WHERE CD.carKey = 129
and SU.sessionStart BETWEEN '11/1/2016' AND '12/1/2016'
)AS T
)k
PIVOT ( MAX(MilesPerCarWeekDay) FOR _WEEKDAY IN ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]) )y1
``````
0

Technology ScientistCommented:
0

Senior DBACommented:
I've avoided using a day name in favor of a numeric method that works under any/all language and date settings.  Since day 0 in SQL Server, 1900-01-01, was a Monday, a simple displacement calc off that date will give the current day of week of any date without resorting to using character strings, which are slower to process in any event.

SELECT
SUM(CASE WHEN DATEDIFF(DAY, 0, SU.sessionStart) % 7 = 0 THEN CD.endmileage - CD.startmileage ELSE 0 END) /
SUM(CASE WHEN DATEDIFF(DAY, 0, SU.sessionStart) % 7 = 0 THEN 1 ELSE 0 END) AS Monday_Avg_MilesDriven,
SUM(CASE WHEN DATEDIFF(DAY, 0, SU.sessionStart) % 7 = 1 THEN CD.endmileage - CD.startmileage ELSE 0 END) /
SUM(CASE WHEN DATEDIFF(DAY, 0, SU.sessionStart) % 7 = 1 THEN 1 ELSE 0 END) AS Tuesday_Avg_MilesDriven,
--...<repeat the pattern for Wednesday thru Saturday>...
SUM(CASE WHEN DATEDIFF(DAY, 0, SU.sessionStart) % 7 = 6 THEN CD.endmileage - CD.startmileage ELSE 0 END) /
SUM(CASE WHEN DATEDIFF(DAY, 0, SU.sessionStart) % 7 = 6 THEN 1 ELSE 0 END) AS Sunday_Avg_MilesDriven
FROM car_details CD
INNER JOIN SessionUnit SU on SU.sessionKey = CD.sessionKey
WHERE CD.carKey = 129
AND SU.sessionStart >= '20161101'
AND SU.sessionStart < '20161201'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.