CODEWITHSUNDEEP
sqlms-accesscountdistinct
Lucia Belardinelli
Lucia Belardinelli

Reputation: 737

Get number of different values in a column in Access

I've tried more or less all combinations of count and distinct (except the correct one :) ) in order to get the example below.

Input: table t1

NAME       |  FOOD
Mary       |  Apple
Mary       |  Banana
Mary       |  Apple
Mary       |  Strawberry
John       |  Cherries

Expected output: 

 NAME      |  FOOD
Mary       |  3
John       |  1

N.B. Mary has Apple in two rows but she has 3 as we have 3 different values in the column. I only managed to get 4 in FOOD Column for her, but I need 3 :(

Upvotes: 1

Views: 631

Answers (3)

Musakkhir Sayyed
Musakkhir Sayyed

Reputation: 7170

select a.name, sum(a.FoodCount) from(
select distinct name,COUNT(food) as FoodCount from #t1 group by name, food ) as a group by a.name order by 2 desc

Upvotes: 0

HansUp
HansUp

Reputation: 97100

Start with a query which gives you unique combinations of NAME and FOOD:

SELECT DISTINCT t1.NAME, t1.FOOD
FROM t1

Then you can use that as a subquery in another where you can GROUP BY and Count:

SELECT sub.NAME, Count(*) AS [FOOD]
FROM
    (
        SELECT DISTINCT t1.NAME, t1.FOOD
        FROM t1
    ) AS sub
GROUP BY sub.NAME;

Upvotes: 1

koushik veldanda
koushik veldanda

Reputation: 1107

select a.name as NAME, a.count(name) as Food 
from 
(SELECT distinct NAME,Food from table)a

Upvotes: 1

Related Questions