Solved

Count query problem

Posted on 2012-04-05
2
260 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
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 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

838 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