CODEWITHSUNDEEP
sqlms-access
user4520
user4520

Reputation: 3457

ORDER BY results of a query (nested?)

Note: I have browsed similar questions, but they didn't answer my question.

To practice SQL, I have created a testing database in MS Access. It stores information about a car shop's business. The layout is like so:

enter image description here

I wish to list (group by them) the car makes with the service that is most frequently bought/ordered for the given make. I don't know how to use the ORDER BY clause here. SO: each make should only be shown once, with its most frequently purchased service.

Example:
Porsche | Oil change
Ford | Tire change
Chevrolet | Brake fluid change
etc.

I suppose I should somehow nest it, but I'm at a loss as to how to exactly do it.

I was able to build the following query, which will list all car-service relations:

SELECT cars.Make, services.[Service name]
FROM (repairs 
INNER JOIN cars ON cars.[Car number] = repairs.[Car number]) 
INNER JOIN services ON services.ID = repairs.[Service ID]
GROUP BY cars.Make, services.[Service name];

enter image description here

How would I build this query properly, as described above?

Upvotes: 0

Views: 150

Answers (1)

bbrumm
bbrumm

Reputation: 1352

Could you try this query, which uses a MAX and COUNT inside subqueries?

SELECT sub1.make, sub1.[Service name]
FROM (
    SELECT cars.Make, services.[Service name], COUNT(*) as COUNTREPAIR
    FROM (repairs 
    INNER JOIN cars ON cars.[Car number] = repairs.[Car number]) 
    INNER JOIN services ON services.ID = repairs.[Service ID]
    GROUP BY cars.Make, services.[Service name]
) sub1
INNER JOIN
    (SELECT sub2.make, MAX(COUNTREPAIR) as MAXCOUNT
    FROM 
        (
        SELECT cars.Make, services.[Service name], COUNT(*) as COUNTREPAIR
        FROM (repairs 
        INNER JOIN cars ON cars.[Car number] = repairs.[Car number]) 
        INNER JOIN services ON services.ID = repairs.[Service ID]
        GROUP BY cars.Make, services.[Service name]
        ) sub2
    GROUP BY sub2.Make
    ) sub3
ON sub1.COUNTREPAIR = sub3.MAXCOUNT
AND sub1.MAKE = sub3.MAKE;

I put together an Access database and some dummy data that mimics yours, so hopefully it gives you the result you need.

If you wanted the COUNT of these services, you can add a "sub1.COUNTREPAIR" at the end of the first line.

Upvotes: 1

Related Questions