Oracle SQL queries (how to average dates)?

[Goride]

I am trying to find the which cats took longer than average to sell. This is what i have so far:


Select SaleAnimal.AnimalID, SaleAnimal.SaleID, Sale.SaleDate, Animal.category
FRom Animal INNER JOIN (SaleAnimal INNER JOIN Sale ON SaleAnimal.SaleID = Sale.SaleID) ON Animal.AnimalID = SaleAnimal.AnimalID
WHERE ((Animal.Category = 'Cat') AND (Sale.SaleDate > (Select AVG(SaleDate) FROM Animal INNER JOIN ( SaleAnimal INNER JOIN Sale ON SaleAnimal.SaleID = Sale.SaleID) ON Animal.AnimalID = SaleAnimal.AnimalID
WHERE (Animal.category = 'Cat'))));

My problem is that it doesn't like how I am trying to average a date. I'm trying to look this up, but I'm not having very goodluck. (i thought I found a site on google, but you have to be a member to view the site)

Does anyone know the syntax to average a date? I believe otherwise this is correct.

 

[ameoba]

FOR THE LOVE OF GOD, FORMAT YOUR CODE.

Unless all the cats came in at the same time, averaging sale_date isn't going to give you anything meaningful. ("average date" is, itself, a fairly unnatural concept)

So, for starters, the length of time it took something to sell would be the differnce between the time the animal came in and the time it was sold. Conveniently, oracle uses numbers for date intervals so you should probably try to start with something like...

...
WHERE (dateA - dateB) > (SELECT avg(dateA-dateB) FROM FOO)
...

 

[Goride]

Thanks for your help, I was looking in the wrong direction. I totally forgot that you cant average Saledate because its just a single date lol.

When I fixed it I had to grab a few other tables. Here is the finished code that I believe is correct after looking at the output, if you care:

Select 
SaleAnimal.AnimalID, SaleAnimal.SaleID, Sale.SaleDate, Animal.category, AnimalOrder.OrderDate

FROM 
AnimalORder INNER JOIN (AnimalOrderItem INNER JOIN (Animal INNER JOIN (SaleAnimal INNER JOIN Sale ON SaleAnimal.SaleID = Sale.SaleID) ON Animal.AnimalID = SaleAnimal.AnimalID) ON AnimalOrderItem.animalID = Animal.animalID) ON AnimalORder.OrderID = AnimalOrderItem.OrderID

WHERE 
((Animal.Category = 'Cat') 
AND 
((Sale.SaleDate - animalOrder.orderdate)  > 

(Select AVG(sale.SaleDate - animalorder.orderdate)
 FROM Animal INNER JOIN ( SaleAnimal INNER JOIN Sale ON SaleAnimal.SaleID = Sale.SaleID) ON Animal.AnimalID = SaleAnimal.AnimalID

WHERE (Animal.category = 'Cat'))));

(I tried to format it the best I could, heh)

 

[ameoba]

Try this...

WITH BigJoin AS
    SELECT SaleAnimal.AnimalID,
            SaleAnimal.SaleID,
            Sale.SaleDate,
            Animal.category,
            AnimalOrder.OrderDate
            (Sale.SaleDate - AnimalOrder.OrderDate) SaleTime
    FROM AnimalORder, AnimalOrderItem, Animal, SaleAnimal, Sale 
    WHERE SaleAnimal.SaleID = Sale.SaleID
    AND Animal.AnimalID = SaleAnimal.AnimalID
    AND AnimalOrderItem.animalID = Animal.animalID
    AND AnimalORder.OrderID = AnimalOrderItem.OrderID
SELECT *
FROM BigJoin
WHERE Category = 'Cat'
AND SaleTime > (SELECT AVG(SaleTime) FROM BigJoin)

Moving the joins to the where clause helps keep the query more readable (you may or may not want to use the Oracle (+) syntax for outer joins. Using the with clause (I think that's Oracle specific) keeps you from having to repeat so much code.

You may want to move the bit about it being a cat into BigJoin or not - it depends on what you're looking for.