SQL Query for AVG

Table A: Column1: DateTime
               Column2: Downtime

I want to display the average downtime over the last month. I want a single value returned.

How do I build this query
adimit19Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
have you tried my query:

select year(datetime), month(datetime), avg(downtime)
from yourtable
group by year(datetime), month(datetime)
0
 
adimit19Author Commented:
I forgot to add another criteria. I would actually want 12 values for the year, but the average calculated monthly.
0
 
Éric MoreauSenior .Net ConsultantCommented:
select year(column1), month(column1), avg(column2)
from yourtable
group by year(column1), month(column1)
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
krtyknmsqlCommented:
I assume the column Downtime has only time value and the query below gives you a expected result.

SELECT 
	YEAR([DateTime]) [Year],
	UPPER(LEFT(DATENAME(MM,[DateTime]),3)) [Month],
	CONVERT(VARCHAR, CAST(AVG(CAST(CAST(LEFT(DownTime, 12) AS DATETIME) AS FLOAT)) AS DATETIME), 114) + '0'
FROM Tab
GROUP BY 
	YEAR([DateTime]) [Year],
	UPPER(LEFT(DATENAME(MM,[DateTime]),3)) [Month]

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
If expressions to group for get more complicated, it is preferrable to use this syntax:
select "year", "month", avg(downtime)
from (
 select "year"=year("DateTime"), "month"=month("DateTime"), downtime
 from tableA
) tbl
group by "year", "month"

Open in new window

This way you do not need to repeat the expression in group by.
0
 
awking00Commented:
What is the datatype of downtime? Some sample values and expected results might help.
0
 
adimit19Author Commented:
downtime is numeric
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.

All Courses

From novice to tech pro — start learning today.