Sql – How to count unique items in field in Access query

ms-accesssql

My Table: table1

ID  Name  Family
1   A     AA
2   B     BB
3   A     AB
4   D     DD
5   E     EE
6   A     AC

SQL command on Access:

select count(*) from table1

Output: ————> True
6 row(s)

I tried to count unique names:

Expected output: 4 row(s)

select count(distinct Name) from table1

Output on Access: ————> Error

What changes do I need to make to my query?

Best Answer

Try this

SELECT Count(*) AS N
FROM
(SELECT DISTINCT Name FROM table1) AS T;

Read this for more info.

Related Solutions

Sql – How to do INSERT into a table records extracted from another table

No "VALUES", no parenthesis:

INSERT INTO Table2(LongIntColumn2, CurrencyColumn2)
SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1;

SQL select join: is it possible to prefix all columns as ‘prefix.*’

It seems the answer to your question is no, however one hack you can use is to assign a dummy column to separate each new table. This works especially well if you're looping through a result set for a list of columns in a scripting language such as Python or PHP.

SELECT '' as table1_dummy, table1.*, '' as table2_dummy, table2.*, '' as table3_dummy, table3.* FROM table1
JOIN table2 ON table2.table1id = table1.id
JOIN table3 ON table3.table1id = table1.id

I realize this doesn't answer your question exactly, but if you're a coder this is a great way to separate tables with duplicate column names. Hope this helps somebody.

Best Answer

Related Solutions

Sql – How to do INSERT into a table records extracted from another table

SQL select join: is it possible to prefix all columns as ‘prefix.*’

Related Topic