CODEWITHSUNDEEP
sqlh2
valik
valik

Reputation: 2094

calculating average of average in sql and getting full join statement

I am calculating average petrol quantity per month from two tables truck and order. So far I am getting average per month, which I am able to get by order by date and average of average in outer SQL.

I want to get the same value, only now it should return all details of orders. I will be using this to map to java using Row mapper for a one to many relationship and a dto class that has list of orders.

select truckId, truckcode, purchasedDate, descriptions, avg(avgPetrolQty) as avgPerMonth from ( 
SELECT  t.truckId, t.truckCode, t.purchasedDate, t.descriptions, 
COALESCE(monthname(orderDate),'Not Announced') as month, 
IFNULL (avg(petrolQty),0) as avgPetrolQty 
from truck t left join orderz o 
on t.truckId = o.truckId 
where t.truckId = :t.truckId 
group by t.truckCode, orderDate, 
year(orderDate) 
) group by truckcode

So far this gives me as expected average per month single value for each truck, but I want to add the remaining details of order to it, like full join which should get by id. I tried this but obviously not accurate

 select t.truckId, t.truckCode, t.purchasedDate, 
t.descriptions, o.orderId, o.petrolQty, o.orderDate, avg(avgPetrolQty) as avgPerMonth, o.truckId from (truck t 
SELECT  t.truckId, t.truckCode, t.purchasedDate, t.descriptions, o.orderId, o.petrolQty, o.orderDate,  o.truckId 
COALESCE(monthname(o.orderDate),'Not Announced') as month,
IFNULL (avg(o.petrolQty),0) as avgPetrolQty  
left join orderz o 
on t.truckId = o.truckId 
where t.truckId = :t.truckId 
group by t.truckId, o.orderDate 
) 
from truck t left join orderz o 
on t.truckId = o.truckId 
where T.truckId = :t.truckId 
group by t.truckId, o.orderDate

Average per month is the most important information. Now I just want to get a full detail of truck (this means and all orders that belong to the truck)

SQL table 

DROP TABLE IF EXISTS truck;
CREATE TABLE truck (
  truckId INT NOT NULL AUTO_INCREMENT,
  truckCode VARCHAR(255) NOT NULL UNIQUE,
  purchasedDate timestamp  NOT NULL,
  descriptions VARCHAR(255) NOT NULL,
  PRIMARY KEY (truckId)
);

DROP TABLE IF EXISTS orderz;
CREATE TABLE orderz (
  orderId INT NOT NULL AUTO_INCREMENT,
  petrolQty DOUBLE NOT NULL,
  orderDate timestamp NOT NULL,
  truckId INT,
  PRIMARY KEY (orderId),
  FOREIGN KEY (truckId) REFERENCES truck(truckId)
  ON UPDATE CASCADE
  ON DELETE CASCADE
);

Upvotes: 1

Views: 77

Answers (1)

valik
valik

Reputation: 2094

To do this your outer query should serve as more or less a copier , so you do all the work in the inner query and copy to outer select statement only thing specific the outer select statement will do is to get average of the average.

select truckId,  truckCode,  purchasedDate, 
descriptions, orderId, petrolQty, orderDate, avg(avgPetrolQty) as avgPerMonth, truckId from (
SELECT  t.truckId, t.truckCode, t.purchasedDate, t.descriptions, o.orderId, o.petrolQty, o.orderDate,
COALESCE(monthname(o.orderDate),'Not Announced') as month,
IFNULL (avg(o.petrolQty),0) as avgPetrolQty  
from truck t left join orderz o 
on t.truckId = o.truckId 
where t.truckId = :t.truckId 
group by  t.truckCode, o.orderId, o.orderDate
) group by truckCode, orderId

And this is how i have mapped it in java using RowMapper and namedParameterJdbcTemplate to get by id

 public TruckFullDetailDto getTruckFullDetailById (final Integer id) {
     SqlParameterSource namedParameterSource =
                new MapSqlParameterSource("t.truckId", id);
       TruckDetailMapper mapper = new TruckDetailMapper();
       namedParameterJdbcTemplate.query(fullTruckDetailsql, namedParameterSource, mapper);

       TruckFullDetailDto truck = mapper.getDetail();
       return truck;
     }

    /**
     * This is mapper maps one to many relationship.
     */
    public class TruckDetailMapper implements RowMapper<TruckFullDetailDto> {

    private TruckFullDetailDto detail;

     public TruckFullDetailDto mapRow(ResultSet rs, int rowNum)
            throws SQLException {

        if (detail == null) {

            this.detail = new TruckFullDetailDto();

            detail.setTruckId(rs.getInt("truckId"));
            detail.setTruckCode(rs.getString("truckCode"));
            detail.setAvgPerMonth(rs.getDouble("avgPerMonth"));
            detail.setDescriptions(rs.getString("descriptions"));
            detail.setPurchasedDate(rs.getDate("purchasedDate"));
        }

         Order order = new Order ();
         order.setOrderId(rs.getInt("orderId"));
         order.setPetrolQty(rs.getDouble("petrolQty"));
         order.setOrderDate(rs.getDate("orderDate"));
         order.setTruckId(rs.getInt("truckId"));

        this.detail.getOrderList().add(order);
        return null;

    }

     private TruckFullDetailDto getDetail() {
        return detail;
     }

  }

Upvotes: 1

Related Questions