By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,829 Members | 1,829 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,829 IT Pros & Developers. It's quick & easy.

Distinct count syntax?

P: n/a
In the first line I'm trying to count the the unqiue values of the ID
column, but I'm getting syntax error, any idea how to format the distinct
count?

SELECT Count(test.ID) AS IDCOUNT FROM (select distinct test.ID FROM test),
Sum(test.balance) AS BAL, test.statement, billing.YYMM
FROM billing INNER JOIN test ON billing.ID = test.ID
WHERE (((test.invoice) Like "X*") AND ((test.due)=0) AND
((test.collections)=0))
GROUP BY test.statement, billing.YYMM;
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Unfortunately, count distinct is simply not provided in Jet. You have to make
a nested query instead, where the first query groups by all the intended group
by fields plus the value in question, then the second includes just the
intended group by fields and includes a simple Count(*) in the Select list.

On Sat, 24 Jan 2004 10:24:22 +0100, "Anne Heddal" <an********@yahoo.com>
wrote:
In the first line I'm trying to count the the unqiue values of the ID
column, but I'm getting syntax error, any idea how to format the distinct
count?

SELECT Count(test.ID) AS IDCOUNT FROM (select distinct test.ID FROM test),
Sum(test.balance) AS BAL, test.statement, billing.YYMM
FROM billing INNER JOIN test ON billing.ID = test.ID
WHERE (((test.invoice) Like "X*") AND ((test.due)=0) AND
((test.collections)=0))
GROUP BY test.statement, billing.YYMM;


Nov 12 '05 #2

P: n/a
Thought I tried that in the syntax below, but I guess my nesting is wrongly
formatted...

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:lj********************************@4ax.com...
Unfortunately, count distinct is simply not provided in Jet. You have to make a nested query instead, where the first query groups by all the intended group by fields plus the value in question, then the second includes just the
intended group by fields and includes a simple Count(*) in the Select list.
On Sat, 24 Jan 2004 10:24:22 +0100, "Anne Heddal" <an********@yahoo.com>
wrote:
In the first line I'm trying to count the the unqiue values of the ID
column, but I'm getting syntax error, any idea how to format the distinct
count?

SELECT Count(test.ID) AS IDCOUNT FROM (select distinct test.ID FROM test),Sum(test.balance) AS BAL, test.statement, billing.YYMM
FROM billing INNER JOIN test ON billing.ID = test.ID
WHERE (((test.invoice) Like "X*") AND ((test.due)=0) AND
((test.collections)=0))
GROUP BY test.statement, billing.YYMM;

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.