CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.4
user776942
user776942

Reputation:

How do I get the average for each make while only considering a dealer's most current inventory?

I have the following table:

CREATE TABLE inventory (
  pk          serial PRIMARY KEY
, make        text
, inventory   numeric
, dealer      text
, updated     date
);

And the following data:

INSERT INTO inventory (make, inventory, dealer, updated) 
VALUES 
('Toyota', 11, 'Jason', '2013-12-31'),
('Toyota', 47, 'Erika', '2013-12-31'),
('Toyota', 18, 'Jason', '2014-12-31'),
('Toyota', 7, 'Jason', '2015-03-31'),
('Toyota', 83, 'James', '2013-12-31'),
('Toyota', 45, 'Erika', '2014-02-28'),
('Honda', 17, 'James', '2013-12-31'),
('Ford', 32, 'Alan', '2015-07-31'),
('BMW', 98, 'Tom', '2013-05-05'),
('Honda', 43, 'Mary', '2014-06-14'),
('Honda', 9, 'Ronald', '2015-06-04'),
('Mercedes', 11, 'Jason', '2014-01-31'),
('Buick', 23, 'Jason', '2014-08-31'),
('Honda', 15, 'Mary', '2015-08-31')

Each dealer uploads their inventory for each make periodically. I'd like to get the average inventory for each make of car but only use the most recent inventory number for each dealer.

I can do it one-by-one for each make:

SELECT make, AVG(inventory) FROM
  (SELECT DISTINCT ON (dealer) dealer, * FROM inventory WHERE make = 'Toyota' ORDER BY dealer, updated DESC) t
GROUP BY make

The downside is I have to run this query one at a time and have to know ahead of time what makes to run....I'd like to just do 1 query and be done with it so that the final outcome is:

make     |   avg
---------|----------
Toyota   |  45.00000
Honda    |  13.66667
Ford     |  32.00000
BMW      |  98.00000
Mercedes |  11.00000
Buick    |  23.00000

Upvotes: 0

Views: 27

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

I think you can just include make in the ORDER BY clause:

SELECT make, AVG(inventory)
FROM (SELECT DISTINCT ON (make, dealer) i.*
      FROM inventory i
      ORDER BY make, dealer, updated DESC
     ) t
GROUP BY make;

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93724

Another way is by using ROW_NUMBER window function to identify the latest inventory per make and dealer combination

SELECT make, 
       Avg(inventory) 
FROM   (SELECT Row_number()OVER(partition BY make, dealer ORDER BY updated DESC)rn, 
               make, 
               inventory, 
               dealer, 
               updated 
        FROM   inventory) A 
WHERE  rn = 1 
GROUP  BY make

SQL FIDDLE DEMO

Upvotes: 1

Related Questions