Link to home
Start Free TrialLog in
Avatar of DaveChoiceTech
DaveChoiceTech

asked on

Count query problem

Suppose I have a table like this,


ProcessDate          FileID
2011-01-01            A0101
2011-01-01            B0101
2011-01-01            C0101
2011-01-02            A0102
2011-01-02            B0102
...
2011-02-01            A0201
2011-02-02      A0202

etc for each day or month where the number of FileID's per day can vary.

I would like to find the month with the maximum number of FileID's

I can write a query like this,


select
Month(ProcessDate) as [Mth],
Count(distinct TRAN_FileID) as [FileCount]
from MyTable
where ProcessDate >= '2011-01-01'
and ProcessDate < '2012-01-01'
Group by Month(ProcessDate)
order by Month(ProcessDate)

from which I can see the maximum number of files for each month

eg.

1            20
2            35
3            10
4            55
etc

Now I want to get the maximum so tried using this query as subquery like this

select Max(FileCount)
from
(
select
Month(ProcessDate) as [Mth],
Count(distinct TRAN_FileID) as [FileCount]
from MyTable
where ProcessDate >= '2011-01-01'
and ProcessDate < '2012-01-01'
Group by Month(ProcessDate)
order by Month(ProcessDate)
)

Unfortunately this gives me this error,
"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

How I can I find the month with the maximum number of FileIDs using MS SQL Server 2005?
Avatar of Ashok
Ashok
Flag of United States of America image

I think this should work.....

select Max(FileCount)
from
(
select
Month(ProcessDate) as [Mth],
Count(distinct TRAN_FileID) as [FileCount]
from MyTable
where ProcessDate >= '2011-01-01'
and ProcessDate < '2012-01-01'
Group by Month(ProcessDate)
)

HTH
Ashok
ASKER CERTIFIED SOLUTION
Avatar of Ashok
Ashok
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial