curiouswebster
asked on
Need multiple Group By's
I have an exceptions log file where I need to group by Message, then by FormattedMessage.
Message: Exception of type 'System.Exception' was thrown.
FormattedMessage: Error while creating Potential Repeat.
Message: Exception of type 'System.Exception' was thrown.
FormattedMessage: HF not found in Salesforce while updating
Message: Exception of type 'System.Exception' was thrown.
FormattedMessage: HF US/09/x293789 not found in Salesforce at the time of Update.
Message: Exception of type 'System.Exception' was thrown.
FormattedMessage: HF not found in Salesforce while updating
Columns I need are Count(Message), Message, Count(FormattedMessage), FormattedMessage
This data set should create 3 rows, since two can be grouped into one row.
I have tried the following but it fails:
Message: Exception of type 'System.Exception' was thrown.
FormattedMessage: Error while creating Potential Repeat.
Message: Exception of type 'System.Exception' was thrown.
FormattedMessage: HF not found in Salesforce while updating
Message: Exception of type 'System.Exception' was thrown.
FormattedMessage: HF US/09/x293789 not found in Salesforce at the time of Update.
Message: Exception of type 'System.Exception' was thrown.
FormattedMessage: HF not found in Salesforce while updating
Columns I need are Count(Message), Message, Count(FormattedMessage), FormattedMessage
This data set should create 3 rows, since two can be grouped into one row.
I have tried the following but it fails:
select count(Message), Message, count(FormattedMessage), FormattedMessage from dbo.SFErrorLog
group by Message, FormattedMessage
order by count(Message) DESC, count(FormattedMessage) DESC
What does the data in that table look like, and what should the result look like? I don't understand your desired result from that.
ASKER
I added columns names to make it more clear.
Displayed in comma, space delimited...
MCount, Message, FMCount, FormattedMessage
4, Exception of type 'System.Exception' was thrown, 2, HF not found in Salesforce while updating
4, Exception of type 'System.Exception' was thrown, 1, Error while creating Potential Repeat
4, Exception of type 'System.Exception' was thrown, 1, HF US/09/x293789 not found in Salesforce at the time of Update.
Notice the message 'HF not found in Salesforce while updating' has multiple hits but it group into one record and shows it has 2 rows found
select count(Message) as MCount , Message as Message, count(FormattedMessage) as FMCount, FormattedMessage as FormattedMessage from dbo.SFErrorLog
group by Message, FormattedMessage
order by count(Message) DESC, count(FormattedMessage) DESC
Displayed in comma, space delimited...
MCount, Message, FMCount, FormattedMessage
4, Exception of type 'System.Exception' was thrown, 2, HF not found in Salesforce while updating
4, Exception of type 'System.Exception' was thrown, 1, Error while creating Potential Repeat
4, Exception of type 'System.Exception' was thrown, 1, HF US/09/x293789 not found in Salesforce at the time of Update.
Notice the message 'HF not found in Salesforce while updating' has multiple hits but it group into one record and shows it has 2 rows found
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Your query looks good. I will run it locally :)
ASKER
The query results look great. Lastly, please explain what the WITH statement is called and what it's doing...
Thanks!
Thanks!
ASKER
Thanks
I started writing a CTE (WITH) and ended up simplifying it. Technically you could do something like this too:
But there's no change in the execution plan between the two.
SELECT sub.MessageCount,sub.Message,COUNT(*) AS "FMCount",sfe.FormattedMessage
FROM SFErrorLog sfe
JOIN (SELECT Message,COUNT(*) AS "MessageCount"
FROM SFErrorLog
GROUP BY Message) sub ON sub.Message = sfe.Message
GROUP BY FormattedMessage,sub.Message,sub.MessageCount
ORDER BY Message,FormattedMessage
But there's no change in the execution plan between the two.