CODEWITHSUNDEEP
sqlh2
valik
valik

Reputation: 2094

calculating average amount of order per month in sql

I have written a query for a h2 db that looks like this 

SELECT o.order_id,  o.order_date , o.petrol_qty,  (o.price) as price_per_litre , (o.price * petrol_qty) as total_Price, t.truck_code
 FROM ORDERZ o left join truck t ON o.truck_id = t.truck_id
group by o.order_id

with a result as such This is for an orderz table 

ORDER_ID ORDER_DATE PETROL_QTY PRICE_PER_LITRE  TOTAL_PRICE     TRUCK_CODE  
1       2005-01-01     12.0     5.8                69.6           BY2354
2       2006-01-21     13.0     2.8                36.4           BY2994
3       2006-01-21     13.0     2.8                36.4           BY8754
4       2006-01-21     13.0     2.8                36.4           BY8754
5       2006-01-21     13.0     2.8                36.4           BY2354

IN TRUCK TABLE I WANT GET average amount of petrol(petrol should be petrol quantity from order table) per month for each truck i am stuck because i dont know exactly how to do a per month average for each truck , any help please.

These are my tables 

CREATE TABLE truck (
  truck_id INT NOT NULL AUTO_INCREMENT,
  truck_code VARCHAR(255) NOT NULL,
  purchased_date VARCHAR(255) NOT NULL,
  description VARCHAR(255) NOT NULL,
  PRIMARY KEY (truck_id)
);

DROP TABLE IF EXISTS order;

CREATE TABLE IF NOT EXISTS orderz (
  order_id INT NOT NULL AUTO_INCREMENT,
  petrol_qty DOUBLE NOT NULL,
  price DOUBLE NOT NULL DEFAULT 0,
  order_date  Date NOT NULL,
  truck_id INT,
  PRIMARY KEY (order_id),
  FOREIGN KEY (truck_id) REFERENCES truck(truck_id)
  ON UPDATE CASCADE
  ON DELETE CASCADE
);

NOte- truck_code is unique i wanted the truck table t to look like truck_id truck_code avg_amount_petrol_qty_per_month, i am using only h2 db

Upvotes: 1

Views: 812

Answers (1)

kc2018
kc2018

Reputation: 1460

The H2 DB documentation has these functions: MONTH(date), YEAR(date) and the aggregate function AVG(). Could use MONTHNAME(date) instead of MONTH(date). Try this?

A) Group by Truck code, Year and Month:

select t.truck_code, year(order_date) as year, month(order_date) as month,
avg(petrol_qty) as avg_petrol_qty from
truck t inner join orderz o
on t.truck_id = o.truck_id 
group by t.truck_code, year(order_date), month(order_date)
order by t.truck_code, year(order_date), month(order_date)

B) Group by Truck code and Month:

select t.truck_code,  month(order_date) as month,
avg(petrol_qty) as avg_petrol_qty from
truck t inner join orderz o
on t.truck_id = o.truck_id 
group by t.truck_code,   month(order_date)
order by t.truck_code,   month(order_date)

Upvotes: 3

Related Questions