Simple count query not so simple...

[Guest]

Let's say I want to count the number of order lines for every order. So I
have an order that has 6 lines on it. I want to count the order as a unique
identity but count all six lines so the query would return

countofORDERS = 1
countofLINES = 6

but what is happening is this

countofORDERS = 6
countofLINES = 6

It's counting the order # 6 times even though it's the same number.
is there such thing as a count distinct or something similiar? I could not
find one.

 

[Michel Walsh]

Hi,


TRANSFORM COUNT(fieldName) As TheValue
SELECT groupName, COUNT(TheValue) As DistinctCount, COUNT(*) As TotalCount
FROM myTable
GROUP BY groupName
PIVOT fieldName IN( null )


would return, per groupName, the number of distinct values of fieldName, and
the number of records.


Alternatively, you can use

SELECT COUNT(*)
FROM ( SELECT DISTINCT fieldName FROM myTable)


to get the total number of distinct values of fieldName (no group).



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Cool it seems to be working but what do i do with this line?

PIVOT fieldName IN( null )

What is the fieldName? and what do i put in place of null?

Thanks for your help.

Chris Ehmen

 

[Michel Walsh]

Hi,

fieldName is the field you wish to count and countDistinct


Hoping it may help,
Vanderghast, Access MVP