CODEWITHSUNDEEP
mysqlsql
ddd
ddd

Reputation: 5029

How to calculate daily average by quarters

I have an Order table in our database which has columns including order_nbr, create_ts, etc.

It is straight forward to calculate the daily average of order count using the following SQL

select avg(cnt)
from 
(  
  select date(create_ts) dt, count(distinct order_nbr)
  from order
  group by date(create_ts)
)

What if I want to plot the daily average vs quarters? How do I estimate the average within a certain period of time? For presentation purpose, it would be a bar chart with daily average as the y axis and quarters (2017Q3, 2017Q4, 2018Q1, 2018Q2) as the x axis.

All I can think of is this is gonna be avg of the avg over a running window which is 90 days long. Another thought is maybe create a Date Dimension table. So every date will have a corresponding quarter name. I am not clear how to write a SQL query for this though.

Upvotes: 0

Views: 990

Answers (3)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

To plot Daily average v/s Quarter, you basically need to calculate average of daily averages in a quarter, and group them based on the quarter.

To get quarter corresponding to a date, you can use the Quarter() function.

Try the following query: 

SELECT 
  YEAR(inner_nest.dt) AS yr, 
  QUARTER(inner_nest.dt) AS qtr, 
  AVG(inner_nest.daily_avg) AS daily_avg_qtrwise 
FROM 
(
 SELECT 
   DATE(create_ts) AS dt, 
   COUNT(DISTINCT order_nbr) AS daily_avg 
 FROM order 
 GROUP BY DATE(create_ts) 
) 
GROUP BY 
  YEAR(inner_nest.dt), 
  QUARTER(inner_nest.dt)

Note that Quarter() function only returns the quarter number for a particular year. So, you will need to use the year number (yr) alongside the quarter number (qtr), to determine the specific quarter.

Upvotes: 1

MatBailie
MatBailie

Reputation: 86735

You could just use the YEAR() and QUARTER() functions and group by them?

SELECT
  YEAR(   DATE(create_ts))   AS YearID,
  QUARTER(DATE(create_ts))   AS QuarterID,
  COUNT(DISTINCT order_nbr)
FROM
  order
GROUP BY
  YEAR(   DATE(create_ts)),
  QUARTER(DATE(create_ts))

Upvotes: 0

Walton Surratt
Walton Surratt

Reputation: 36

You only need to add one section to the query to filter the results by a range of dates. Apply the WHERE statement to pick the fields and results to filter, then use the AND statement to apply additional filters.

For example:

select avg(cnt)
from
(
  select date(create_ts) dt, count(distinct order_nbr)
  from order
  where date(create_ts) >= date(START_DATE) and date(create_ts) <= date(END_DATE)
  group by date(create_ts)
)

This means that you'll need an individual query for each quarter.

Upvotes: 0

Related Questions