Simple count query not so simple...

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
Cool it seems to be working but what do i do with this line?
PIVOT fieldName IN( null )
Click to expand...
What is the fieldName? and what do i put in place of null?

Thanks for your help.

Chris Ehmen
 
Hi,

fieldName is the field you wish to count and countDistinct


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top