CODEWITHSUNDEEP
sqlms-accessnested
Geoffrey
Geoffrey

Reputation: 5432

How can I nest these two SQL queries in MS Access 2007?

I have a table called baskets with these columns:

With Query1, I determine the total number of fruit in each basket and I also include the colour of each basket:

SELECT basket, colour, apples+bananas+oranges+pears+peaches AS fruit
FROM baskets;

Query1 consists of three columns:

With Query2, I determine the average number of fruits there are in all baskets of each colour by drawing the information from the result of Query1:

SELECT DISTINCT
        candidate.colour,
        candidate.fruit
            (SELECT AVG(fruit)
                 FROM Query1 AS average
                 WHERE average.colour = candidate.colour) AS fruit
    FROM Query1 AS candidate;

Query2 consists of two columns:

Is it possible to nest these queries so that I may obtain the result of Query2 with only one query?

Your help will be much appreciated. Thank you.

Upvotes: 1

Views: 981

Answers (2)

DJ.
DJ.

Reputation: 16267

If you want the total fruit by colour of basket you would do something like this:

SELECT colour, SUM(apples+bananas+oranges+pears+peaches) AS totalfruit
FROM baskets
GROUP By colour

Upvotes: 1

Alex Martelli
Alex Martelli

Reputation: 882791

SELECT colour, AVG(apples+bananas+oranges+pears+peaches) AS fruit
FROM baskets
GROUP by colour;

Upvotes: 2

Related Questions