Multiple Group By Functions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple Group By Functions

Author

Topic

robbied81
Starting Member

9 Posts

Posted - 2010-05-27 : 07:48:47

Hi,

Can someone help me. I have a table which we use for error logs. 2 of the columns are named errorFile and errorDesc. I want to a query where I can get a count of the top 10 errorDesc along with the errorFiles?

How can I go about this in SQL 2005.

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-27 : 08:02:39

Can you please give sample data and wanted result?

No, you're never too old to Yak'n'Roll if you're too young to die.

robbied81
Starting Member

9 Posts

Posted - 2010-05-27 : 08:12:56

quote:
Originally posted by webfred

Can you please give sample data and wanted result?

No, you're never too old to Yak'n'Roll if you're too young to die.

Hi,

I have uploaded file, you will need to go here to download:

[url]http://www.mediafire.com/?tz2tmqmz1zn[/url]

The file is called sample.xlsx.

This is an extraction from sql.

Basically I want a query that gives me the top 10 error descriptions, error files and the amount of times they occur.

Thanks

Rob

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-27 : 08:57:08

Do you mean this?

select top 10 * from
(
select errorDesc, errorFile, count(*) as cnt
from table
group by errorDesc, errorFile
)dt
order by cnt DESC

No, you're never too old to Yak'n'Roll if you're too young to die.

robbied81
Starting Member

9 Posts

Posted - 2010-05-27 : 10:06:49

quote:
Originally posted by webfred

Do you mean this?

select top 10 * from
(
select errorDesc, errorFile, count(*) as cnt
from table
group by errorDesc, errorFile
)dt
order by cnt DESC

No, you're never too old to Yak'n'Roll if you're too young to die.

Thank you so much. Works perfectly. Means I can expand my search to top 100 etc.

Much appreciated.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-27 : 10:10:46

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-27 : 10:19:45

or you can flatten it into


select top 10 errorDesc, errorFile, count(*) as cnt
from   table
group by errorDesc, errorFile
order by cnt desc

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources