CODEWITHSUNDEEP
sqlms-access
Chris
Chris

Reputation: 67

Writing a Query with Distinct Count in Access

I'm having some difficulty with a query that I'm writing that is meant to display the "makers" that produce laptops with 3 or more DIFFERENT speeds.

SELECT DISTINCT Product.maker, Count(Laptop.speed) AS [3+ Different Speeds]
FROM Laptop INNER JOIN Product ON Laptop.model = Product.model
WHERE type = "laptop"
GROUP BY Product.maker
HAVING Count(*) >= 3;

This gives me the 2 correct "makers" that produce 3 or more laptop models, however, one "maker", A, produces two laptop models with the SAME speed, so that "maker" needs to be eliminated from the resulting table, which is below.

maker   3+ Different Speeds
  A             3
  E             3

Here's the Laptop table where the model and speed data is stored:

model   speed
2001    2.00   E
2002    1.73   E
2003    1.80   E
2004    2.00   A
2005    2.16   A
2006    2.00   A

E produces the top 3, and A produces the bottom 3. I'm fairly certain that I need to make my Count function DISTINCT, however, I know that Access does not support DISTINCT Count. Any assistance/suggestions would be greatly appreciated!

Upvotes: 0

Views: 136

Answers (3)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17137

The below query should solve your problem.

SELECT
    maker,
    COUNT(speed)
FROM(
    SELECT
        p.maker,
        l.speed
    FROM
        Laptop l
        INNER JOIN Product p
            ON l.model = p.model
    WHERE
        type = "laptop"
    GROUP BY 1,2
    ) foo
GROUP BY 1
HAVING COUNT(1) >= 3

First you aggregate all speeds for the same maker, so the inner query would produce:

 maker | speed | count 
-------+-------+-------
 E     |  1.73 |     1
 E     |  1.80 |     1
 A     |  2.16 |     1
 E     |  2.00 |     1
 A     |  2.00 |     2

Now you have distinct rows for each pair (maker, speed) so that you can simply run a COUNT() over speed.

Result

 maker | count 
-------+-------
 E     |     3
 A     |     2

Now eliminate A with HAVING clause.

Upvotes: 1

Chris
Chris

Reputation: 67

This is the WORKING query that I came up with, a slightly modified version of what you provided me with.

SELECT P.maker AS [Maker], Count(L.speed) AS [3+ Different Speeds]
FROM (SELECT P.maker, L.speed FROM Laptop AS L INNER JOIN Product AS P ON L.model = P.model
WHERE type = "laptop"
GROUP BY P.maker, L.speed)
GROUP BY [Maker]
HAVING Count(L.speed) >= 3;

Thanks again!

Upvotes: 0

Andrei Hirsu
Andrei Hirsu

Reputation: 696

Your query will work fine if you take out the distinct keyword. If you only group by the maker, then that query will bring you back only one record per maker, the distinct is actually redundant.

Upvotes: 0

Related Questions