Have Geek Brain

Combining multiple SQL queries

I want to make an inquiry to list cats that took longer than regular cats to sell? I have five tables:

Animal, Sale, AnimalOrderItem, AnimalOrder and SaleAnimal

Animal table: AnimalID, name, category (cat, dog, fish)

SaleAnimal table: SaleID, AnimalID, SalePrice

Sales table: SaleID, date, employeeID, CustomerID

AnimalOrderItem table: OrderID, AnimalID, cost

AnimalOrder: OrderID, OrderDate, Received Date, ProviderID, ShippingCost, EmployeeID

There are other tables that I don't think they affect the query.

I thought about the following ... make a request to calculate days to sell for all ex .:

[SaleDate]-[ReceiveDate] AS DaysToSell
Have the INNER JOIN built:
Sale INNER JOIN ((AnimalOrder INNER JOIN (Animal INNER JOIN AnimalOrderItem
ON Animal.AnimalID = AnimalOrderItem.AnimalID) ON AnimalOrder.
OrderID = AnimalOrderItem.OrderID) INNER JOIN SaleAnimal ON Animal.
AnimalID = SaleAnimal.AnimalID) ON Sale.SaleID = SaleAnimal.SaleID

Create another query based on the above query

SELECT AnimalID, Name, Category, DaysToSell
WHERE Category="Cat" AND DaysToSell>
(SELECT Avg(DaysToSell)
FROM the earlier query
WHERE Category="Cat"
ORDER BY DaysToSell DESC;

After running the request, I got the error

ORA-00921 unexpected end of SQL command

Any suggestions! you are welcome

0


a source to share


3 answers


Queries can be combined with a subquery. For instance,

select *
from (
    select * 
    from mytable
) subquery


Applying this pattern to your problem seems pretty straightforward.

+1


a source


I don't see a closed parenthesis that matches the selected avg



0


a source


Ok, I came up with this:

    SELECT AnimalID, Name, Category,
           [SaleDate]-[ReceiveDate] AS DaysToSell
    FROM   Sale INNER JOIN ((AnimalOrder INNER JOIN (Animal INNER JOIN AnimalOrderItem ON Animal.AnimalID = AnimalOrderItem.AnimalID) ON AnimalOrder.OrderID = AnimalOrderItem.OrderID)
           INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID) ON Sale.SaleID = SaleAnimal.SaleID
    WHERE  Category = "Cat"
    AND    ([SaleDate]-[ReceiveDate]) > (SELECT AVG([SaleDate]-[ReceiveDate])
                                         FROM   Sale INNER JOIN ((AnimalOrder INNER JOIN (Animal INNER JOIN AnimalOrderItem ON Animal.AnimalID = AnimalOrderItem.AnimalID) ON AnimalOrder.OrderID = AnimalOrderItem.OrderID)
                                         INNER JOIN SaleAnimal ON Animal.AnimalID =SaleAnimal.AnimalID) ON Sale.SaleID = SaleAnimal.SaleID
                                         WHERE Category = "Cat")
    ORDER BY ([SaleDate]-[ReceiveDate]) DESC;
0


a source






More articles:


All Articles