Solved

sql calculate averages

Posted on 2016-11-29
18
55 Views
Last Modified: 2016-12-28
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 Users U on U.userKey = CD.updateuser
					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

Open in new window

0
Comment
Question by:erikTsomik
  • 9
  • 6
  • 2
  • +1
18 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41906051
Are you getting any error or the calculation is coming incorrect ?
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 41906104
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 Users U on U.userKey = CD.updateuser
				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

Open in new window

0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41906107
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 Users U on U.userKey = CD.updateuser
					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

Open in new window

0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 19

Author Comment

by:erikTsomik
ID: 41906112
on this line SELECT milesDriven, getting the error message Invalid column name 'milesDriven'.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41906115
Can you provide us some data to try ?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41906120
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 Users U on U.userKey = CD.updateuser
					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

Open in new window

0
 
LVL 19

Author Comment

by:erikTsomik
ID: 41906122
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

Open in new window

0
 
LVL 19

Author Comment

by:erikTsomik
ID: 41906125
carkey	TotalMilesDriven	MilesPerCarWeekDay
129	183	26
129	183	27
129	183	40
129	183	90

Open in new window

0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41906126
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 Users U on U.userKey = CD.updateuser
					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

Open in new window

0
 
LVL 19

Author Comment

by:erikTsomik
ID: 41906129
carkey	_WEEKDAY	TotalMilesDriven	MilesPerCarWeekDay
129	Monday	183	27
129	Saturday	183	40
129	Sunday	183	90
129	Tuesday	183	26

Open in new window

0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41906149
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

Open in new window



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)

--

Open in new window


Hope it helps !!
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41906152
I shall wait for some time if any changes are reqd.
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 41906159
Well I need average not the totals
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 41906161
what about my query without using a temp table
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41906162
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

Open in new window

0
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41906169
Try.. with your query
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 Users U on U.userKey = CD.updateuser
		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

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 41906174
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41906217
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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Webservices in T-SQL 3 31
Why i am getting a star, SSMS does not show me any error. Division Error 5 22
SQL view 2 27
Help in Bulk Insert 9 33
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question