Dewie Dewie - 2 months ago 5
SQL Question

How to find the average of an item in sql database

Ok using Sally's Pet Store database I am trying to figure out what cats that took longer than average cats to sell. I'm thinking that it should start out like this maybe.....

SELECT AVG (

SaleDate
) AS
Longer to Sale
....but I get confused on how to get the AVG when there are nothing in the database that would tell me. Here is the database in Access Relationship form so everyone could see enter image description here

I think the only tables that would be used is: Animals, SaleAnimal, AnimalOrderItem.

But some help would be thankful and maybe a good explanation on getting it

Answer

Step 1: What is the average time to sell a cat?

DECLARE @avg INT;

SELECT @avg = AVG(DATEDIFF(day, or.ReceiveDate, s.SaleDate))
FROM Animal a
   INNER JOIN SaleAnimal asale ON (a.AnimalID = asale.AnimalID)
   INNER JOIN Sale s ON (asale.SaleID = s.SaleID)
   INNER JOIN AnimalOrderItem aoi ON (a.AnimalID = aoi.AnimalID)
   INNER JOIN AnimalOrder ao ON (aoi.OrderID = ao.OrderID)
WHERE a.Breed = 'cat';

Step 2: How many cats took longer to sell?

SELECT COUNT(1)
FROM Animal a
   INNER JOIN SaleAnimal asale ON (a.AnimalID = asale.AnimalID)
   INNER JOIN Sale s ON (asale.SaleID = s.SaleID)
   INNER JOIN AnimalOrderItem aoi ON (a.AnimalID = aoi.AnimalID)
   INNER JOIN AnimalOrder ao ON (aoi.OrderID = ao.OrderID)
WHERE a.Breed = 'cat'
   AND DATEDIFF(day, or.ReceiveDate, s.SaleDate) > @avg;
Comments