[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

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
0
adimit19
Asked:
adimit19
1 Solution
 
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
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
QlemoDeveloperCommented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
have you tried my query:

select year(datetime), month(datetime), avg(downtime)
from yourtable
group by year(datetime), month(datetime)
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now