SQL MS ACCESS：如何使用访问查询计算不同的值？

MS ACCESS: How can i count distinct value using access query?

提问by Sadat

here is the current complex query given below.

这是下面给出的当前复杂查询。

SELECT DISTINCT Evaluation.ETCode, Training.TTitle, Training.Tcomponent, Training.TImpliment_Partner, Training.TVenue, Training.TStartDate, Training.TEndDate, Evaluation.EDate, Answer.QCode, Answer.Answer, Count(Answer.Answer) AS [Count], Questions.SL, Questions.Question
FROM ((Evaluation INNER JOIN Training ON Evaluation.ETCode=Training.TCode) INNER JOIN Answer ON Evaluation.ECode=Answer.ECode) INNER JOIN Questions ON Answer.QCode=Questions.QCode
GROUP BY Evaluation.ETCode, Answer.QCode, Training.TTitle, Training.Tcomponent, Training.TImpliment_Partner, Training.Tvenue, Answer.Answer, Questions.Question, Training.TStartDate, Training.TEndDate, Evaluation.EDate, Questions.SL
ORDER BY Answer.QCode, Answer.Answer;

There is an another column Training.TCode. I need to count distinct Training.TCode, can anybody help me? If you need more information please let me know

还有另一列 Training.TCode。我需要计算不同的 Training.TCode，有人可以帮我吗？如果您需要更多信息，请告诉我

采纳答案by Rippo

try

尝试

select ..., count(distinct Training.Tcode) as ..., ...

EDIT - please now look at this...

编辑 - 请现在看看这个...

Take the following SQL code. The first select is how SQL server would do this and the second query should be access compliant...

获取以下 SQL 代码。第一个选择是 SQL 服务器将如何执行此操作，第二个查询应该是访问兼容的...

declare @t table (eCode int, tcode int)
insert into @t values(1,1)
insert into @t values(1,1)
insert into @t values(1,2)
insert into @t values(1,3)
insert into @t values(2,2)
insert into @t values(2,3)
insert into @t values(3,1)    

select 
    ecode, count(distinct tCode) countof
from
    @t
group by
    ecode

select ecode, count(*)
from
    (select distinct tcode, ecode
    from  @t group by tcode, ecode) t
group by ecode

It returns the following:

它返回以下内容：

ecode tcode
1       3 (there are 3 distinct tcode for ecode of 1)
2       2 (there are 2 distinct tcode for ecode of 2)
3       1 (there is 1 distinct tcode for ecode of 3)

回答by Patrick Honorez

I posted a similar question about a year ago in Google groups. I received an excellent answer:

大约一年前，我在 Google 群组中发布了一个类似的问题。我收到了一个很好的答案：

---

---

A crosstab can do (from an original proposition from Steve Dassin) as long as you count either the fund, either the subfund:

只要您计算基金或子基金，交叉表就可以（来自 Steve Dassin 的原始提议）：

  TRANSFORM COUNT(*) AS theCell
  SELECT ValDate,
      COUNT(*) AS StandardCount,
      COUNT(theCell) AS DistinctCount
  FROM tableName
  GROUP BY ValDate
  PIVOT fund IN(Null)

which, for each day (group), will return the number of records and the number of different (distinct) funds.

对于每一天（组），它将返回记录数量和不同（不同）资金的数量。

Change

改变

PIVOT fund IN(Null)

to

到

PIVOT subfund IN(Null)

to get the same, for sub-funds.

得到同样的，对于子基金。

Hoping it may help, Vanderghast, Access MVP

希望它可以帮助，Vanderghast，Access MVP

---

---

I don't know if that will work, but here's a link to that post.

我不知道这是否可行，但这是该帖子的链接。

回答by Gordon Bell

Sadat, use a subquery like this:

Sadat，使用这样的子查询：

SELECT DISTINCT Evaluation.ETCode, Training.TTitle, Training.Tcomponent, Training.TImpliment_Partner, Training.TVenue, Training.TStartDate, Training.TEndDate, Evaluation.EDate, Answer.QCode, Answer.Answer, Count(Answer.Answer) AS [Count], Questions.SL, Questions.Question,
(SELECT COUNT(*) FROM Training t2 WHERE t2.TCode = Evalution.ETCode) as TCodeCount
FROM ((Evaluation INNER JOIN Training ON Evaluation.ETCode=Training.TCode) INNER JOIN Answer ON Evaluation.ECode=Answer.ECode) INNER JOIN Questions ON Answer.QCode=Questions.QCode
GROUP BY Evaluation.ETCode, Answer.QCode, Training.TTitle, Training.Tcomponent, Training.TImpliment_Partner, Training.Tvenue, Answer.Answer, Questions.Question, Training.TStartDate, Training.TEndDate, Evaluation.EDate, Questions.SL
ORDER BY Answer.QCode, Answer.Answer;

回答by DBMarcos99

I managed to do a count distinct value in Access by doing the following:

通过执行以下操作，我设法在 Access 中计算了不同的值：

select Job,sum(pp) as number_distinct_fruits

from

(select Job, Fruit, 1 as pp

from Jobtable group by Job, Fruit) t

group by Job

You have to be careful as if there is a blank/null field (in my code fruit field) the group by will count that as a record. A Where clause in the inner select will ignore those though. I've put this on my blog, but am concerned that I've discovered the answer too easily - others here seem to think that you need two sub queries to make this work. Is my solution viable? Distinct groupings in Access

您必须小心，因为如果有一个空白/空字段（在我的代码水果字段中），则 group by 会将其计为记录。内部选择中的 Where 子句将忽略这些。我已经把这个放在我的博客上，但我担心我太容易找到答案了——这里的其他人似乎认为你需要两个子查询才能完成这项工作。我的解决方案可行吗？ Access 中的不同分组

回答by SatyaJohnny

I would propose

我会建议

select R_rep,sum(pp) as number_distinct_Billnos from (select R_rep, Billno, 1 as pp from `Vat_Sales` group by R_rep, Billno) t group by R_rep

回答by Kevin LaBranche

Have a look at this blog entry, it appears you can do this with subqueries....

看看这个博客条目，看来你可以用子查询来做到这一点......

http://blogs.msdn.com/access/archive/2007/09/19/writing-a-count-distinct-query-in-access.aspx

http://blogs.msdn.com/access/archive/2007/09/19/writing-a-count-distinct-query-in-access.aspx

回答by Charles Bretana

try this:

尝试这个：

SELECT DISTINCT e.ETCode, t.TTitle, t.Tcomponent, 
      t.TImpliment_Partner, t.TVenue, t.TStartDate, 
      t.TEndDate, e.EDate, a.QCode, a.Answer, 
      q.SL, q.Question,
      Count(a.Answer) AnswerCount,
      Min(Select Count(*) 
       From (Select Distinct TCode From Training) As Z ) TCodeCount    
FROM Evaluation As e 
   JOIN Training AS t ON e.ETCode=t.TCode
   JOIN Answer AS a ON e.ECode=a.ECode
   JOIN Questions AS q ON a.QCode=q.QCode
GROUP BY e.ETCode, a.QCode, t.TTitle, t.Tcomponent, 
     t.TImpliment_Partner, t.Tvenue, a.Answer, q.Question, 
     t.TStartDate, t.TEndDate, Evaluation.EDate, q.SL
ORDER BY a.QCode, a.Answer;