Lounge - Count distinct

Asked By caishan tan
04-Jun-09 02:04 AM

I have a rather complicated query involving a left join:

SELECT QuarterWk.Week, Count(WkCounterMFG.BatchID) AS CountOfBatchID, Sum(WkCounterMFG.DocAcc) AS SumOfDocAcc, NZ(Sum([#TotalObs]),0) AS TotalErr, IIf(Count([BatchID])=0,0,[TotalErr]/Count([BatchID])) AS ErrPerBatch, QuarterWk.Quarter
FROM QuarterWk LEFT JOIN WkCounterMFG ON QuarterWk.Week = WkCounterMFG.RepWk
GROUP BY QuarterWk.Week, QuarterWk.Quarter
HAVING (((QuarterWk.Quarter) Like [Forms]![RFTWkRepFormMFG]![Quarter]));

what i want basically is to count distinctly the information in bold.. I am not sure how to work with subqueries as i've searched online and it seems like its the only way to go..

Thanks for any help! =)

  Santhosh N replied to caishan tan
04-Jun-09 02:10 AM

A sample way of using sub query here would be as

SELECT QuarterWk.Week,CountOfBatchID,SumOfDocAcc,TotalErr,ErrPerBatch,QuarterWk.Quarter

FROM

(SELECT QuarterWk.Week, Count(WkCounterMFG.BatchID) AS CountOfBatchID, Sum(WkCounterMFG.DocAcc) AS SumOfDocAcc, NZ(Sum([#TotalObs]),0) AS TotalErr, IIf(Count([BatchID])=0,0,[TotalErr]/Count([BatchID])) AS ErrPerBatch, QuarterWk.Quarter
FROM QuarterWk LEFT JOIN WkCounterMFG ON QuarterWk.Week = WkCounterMFG.RepWk
GROUP BY QuarterWk.Week, QuarterWk.Quarter
HAVING (((QuarterWk.Quarter) Like [Forms]![RFTWkRepFormMFG]![Quarter]))

);

and if you want anly the count, then you can use this

SELECT CountOfBatchID

FROM

(SELECT QuarterWk.Week, Count(WkCounterMFG.BatchID) AS CountOfBatchID, Sum(WkCounterMFG.DocAcc) AS SumOfDocAcc, NZ(Sum([#TotalObs]),0) AS TotalErr, IIf(Count([BatchID])=0,0,[TotalErr]/Count([BatchID])) AS ErrPerBatch, QuarterWk.Quarter
FROM QuarterWk LEFT JOIN WkCounterMFG ON QuarterWk.Week = WkCounterMFG.RepWk
GROUP BY QuarterWk.Week, QuarterWk.Quarter
HAVING (((QuarterWk.Quarter) Like [Forms]![RFTWkRepFormMFG]![Quarter]))

);

Hmm..  Hmm..

04-Jun-09 02:27 AM

Hmm.. The output results still gives me the same count value of 11 instead of 8 (i.e. there are only 8 distinct BatchID)... Because the CountOfBatchID is already counting duplicated values of BatchID from the source table.. so wouldn't creating another query to select just reflect the same values? Thanks!

Use distinct...  Use distinct...

04-Jun-09 02:38 AM

SELECT CountOfBatchID

FROM

(SELECT QuarterWk.Week, Count(distinct WkCounterMFG.BatchID) AS CountOfBatchID, Sum(WkCounterMFG.DocAcc) AS SumOfDocAcc, NZ(Sum([#TotalObs]),0) AS TotalErr, IIf(Count([BatchID])=0,0,[TotalErr]/Count([BatchID])) AS ErrPerBatch, QuarterWk.Quarter
FROM QuarterWk LEFT JOIN WkCounterMFG ON QuarterWk.Week = WkCounterMFG.RepWk
GROUP BY QuarterWk.Week, QuarterWk.Quarter
HAVING (((QuarterWk.Quarter) Like [Forms]![RFTWkRepFormMFG]![Quarter]))

);

not working..  not working..
04-Jun-09 02:49 AM

Hmmm tried it before but it wouldn't work... read online that this distinct function is supported in SQL but not Access?

  Santhosh N replied to caishan tan
04-Jun-09 04:32 AM
Create New Account
help
you can demonstrate these things with a previous substantial piece of work, it is only left for the employer to take your word for it. I don't know many employers the case these days. You don't sit down with your new x86 in the lounge and try to write HELLO WORLD in BASIC any more. The fundemantals are hidden from were even beeing sold to third partys they asked me if i would like to join there development team . The team existed out of 2 proggers and a designer , these university
JOIN order - never matters? SQL Server To the best of my knowledge, the following are always equivalent (INNER & LEFT join can be specified in any order, results will always be the same). SELECT . . . . FROM . . . INNER JOIN A. . . LEFT JOIN B. . . LEFT JOIN C SELECT . . . . FROM . . . LEFT JOIN C INNER JOIN A. . . LEFT JOIN B. . . I am not
Is there a difference between LEFT JOIN and LEFT OUTER JOIN? SQL Server Is there a difference between LEFT JOIN and LEFT OUTER JOIN? thx SQL Server Programming Discussions SQL Server (1) INNER JOIN (1) LEFT JOIN (1) Stole
LEFT JOIN question SQL Server I'm a newcomer to T-SQL and have the following simple query. When I run the query without the LEFT JOIN I get 96 more recrods returned than when I include the LEFT JOIN. My (very limited) understanding of LEFT JOIN led me to believe that I would still receive the same number of records as
Left join question SQL Server Hi, Just a general question about left outer join: select a.*, b.columna, c.columnb from a left join b on a.id1 = b.id left join c on a.id2 = c.id Will number of result set rows ALWAYS be the