help about SQL for ACCESS - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

help about SQL for ACCESS

help about SQL for ACCESS# Database - 数据库

g*o2002-10-09 07:10

1 楼

Does anybody help me take a look of following SQL statement
select distinct count(column_name) from table name;
what I want is the unique count number for given column. but it returns
me all of the count ( not unique count).
This statement works fine against Oracle, but not for ACCESS.
thanks very much.

g*o2002-10-09 07:10

2 楼

finally got the answer.
MS ACCESS does not support SELECT COUNT(DISTINCT COLUMN_NAME ) FROM
TABLE_NAME,
but we can use subquery to get the DISTINCT COUNT NUMBER.
the query is
SELECT COUNT(column_name) FROM
(SELECT DISTINCT column_name FROM table_name).

【在 g***o 的大作中提到】

: Does anybody help me take a look of following SQL statement
: select distinct count(column_name) from table name;
: what I want is the unique count number for given column. but it returns
: me all of the count ( not unique count).
: This statement works fine against Oracle, but not for ACCESS.
: thanks very much.

z*e2002-10-09 07:10

3 楼

SELECT COUNT(column_name) FROM table_name GROUP BY column_name
Is it same?

【在 g***o 的大作中提到】

: finally got the answer.
: MS ACCESS does not support SELECT COUNT(DISTINCT COLUMN_NAME ) FROM
: TABLE_NAME,
: but we can use subquery to get the DISTINCT COUNT NUMBER.
: the query is
: SELECT COUNT(column_name) FROM
: (SELECT DISTINCT column_name FROM table_name).

g*o2002-10-09 07:10

4 楼

I think these two queries get different recordsets.
for instance, we have following table
county company
2 A
3 A
4 A
2 B
3 B
4 B
5 B
2 C
your query get this result:
county count
2 3
3 2
4 2
5 1
my query get the result like:
count
4
the reason is that I get the count number of distinct county that exists in
this table,
while you get the count numb

【在 z****e 的大作中提到】

: SELECT COUNT(column_name) FROM table_name GROUP BY column_name
: Is it same?