Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Count query problem

Posted on 2012-04-05
2
Medium Priority
?
270 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 13

Expert Comment

by:Ashok
ID: 37812160
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 2000 total points
ID: 37812763
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

596 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