CODEWITHSUNDEEP
mysqlsql
arvind
arvind

Reputation: 1493

compare aggregates with row data

I have a table in MySql related to purchase of Inventory.

The table format is as below :

A report is to be generated that must have the following columns

Item     HighestRate     HighestRateDate        LowestRate      LowestRateDate    % difference

--
Item  is the itemname
HighestRate and HighestRateDate are rates at maximum
LowestRate and Date are rates at minimum
%difference is a basic difference percentage between highestrate and lowestrate of a row

I have prepared the following query

SELECT itemname,rate,pdate  from purchases 
group by itemname
having rate = max(rate)
order by itemname

which does generate one half of the report.

However since it requires both the lowest and highest rate. This report is incomplete and printing two reports makes comparison difficult.

Any help would be appreciated.

Thankyou

Upvotes: 1

Views: 144

Answers (2)

Andreas Wederbrand
Andreas Wederbrand

Reputation: 40021

Here goes. It's not pretty but it works.

select min.itemname, 
       min.rate, 
       min.pdate, 
       max.rate, 
       max.pdate, 
       (max.rate - min.rate)/max.rate as diff 
  from (SELECT p.itemname, 
               p.rate,
               pdate 
          from purchases p, 
               (select itemname, 
                       max(rate) as rate 
                  from purchases 
                 group by itemname) max_values 
         where p.itemname = max_values.itemname 
           and p.rate = max_values.rate
       ) max, 
       (SELECT p.itemname, 
               p.rate,
               pdate 
          from purchases p, 
               (select itemname, 
                       min(rate) as rate 
                  from purchases 
                 group by itemname) min_values 
         where p.itemname = min_values.itemname 
           and p.rate = min_values.rate
       ) min 
  where max.itemname = min.itemname;

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

You'll need an index on (itemname, rate) for this query not to be awfully slow.

If there are two or more dates with same maximum (or minimum) rate, then a (more or less) random date will be selected. If you want to control that, change the ORDER BY rate ASC (to ORDER BY rate ASC, pdate DESC for example):

SELECT 
    di.itemname  AS Item
  , maxr.rate    AS HighestRate
  , maxr.pdate   AS HighestRateDate
  , minr.rate    AS LowestRate
  , minr.pdate   AS LowestRateDate
  , (maxr.rate - minr.rate)/maxr.rate        --- calculations
                 AS PrecentDifference
FROM
    ( SELECT DISTINCT itemname
      FROM purchases 
    ) AS di
  JOIN
    purchases AS minr
      ON  minr.id =                          --- I guess this is the PK
          ( SELECT id
            FROM purchases p
            WHERE p.itemname = di.itemname 
            ORDER BY rate ASC
            LIMIT 1
          )
  JOIN 
    purchases AS maxr
      ON  maxr.id =                      
          ( SELECT id
            FROM purchases p
            WHERE p.itemname = di.itemname 
            ORDER BY rate DESC
            LIMIT 1
          )

Upvotes: 1

Related Questions