Solved

Count query problem

Posted on 2012-04-05
2
257 Views
Last Modified: 2012-04-05
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?
0
Comment
Question by:DaveChoiceTech
  • 2
2 Comments
 
LVL 13

Expert Comment

by:Ashok
Comment Utility
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
0
 
LVL 13

Accepted Solution

by:
Ashok earned 500 total points
Comment Utility
select Mth, 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)
) a
WHERE FileCount = (
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)
) b );
 
HTH
Ashok
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now