April 30, 2008 at 9:27 am
I have a query that counts data in my database...but I also need to find the max value of that counted data. However when I try, I get an error like this:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Here is my query:
SELECT COUNT(EL.eventTargetId) AS NumberofHits, MAX(COUNT(EL.eventTargetId)) AS Max
FROM na_production.dbo.EventLog EL
LEFT OUTER JOIN na_production.dbo.Pagelist PL ON EL.eventTargetId = PL.pageId
LEFT OUTER JOIN folderList FL ON FL.folderID = PL.folderID
WHERE PL.moduleID = 576456718 AND PL.pageSequence > 0 AND PL.hidden <> 1
GROUP BY EL.eventTargetId
Is there a way to find the maximum of my counted data?
Thanks!
April 30, 2008 at 9:47 am
You want to encapsulate the grouped count in a sub-query, and THEN run the MAX against the derived results of the sub-query. Or - depending on exactly what you're looking to do - there might be other "non-aggregate" ways to do that....
In english - what is it you're trying to get? the one event with the highest count?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 30, 2008 at 9:59 am
Matt, thanks for the response!
I am trying to get the number of hits for each folder. And I am also trying to get the folder that has the maximum number of hits.
I can get the number of hits for each folder, but when trying to add MAX, is when I get the error.
So would my query look something like this?
SELECT MAX(SELECT COUNT(EL.eventTargetId) AS NumberofHits FROM na_production.dbo.EventLog EL
LEFT OUTER JOIN na_production.dbo.Pagelist PL ON EL.eventTargetId = PL.pageId
LEFT OUTER JOIN folderList FL ON FL.folderID = PL.folderID
WHERE PL.moduleID = 576456718 AND PL.pageSequence > 0 AND PL.hidden <> 1
GROUP BY EL.eventTargetId) As Max
April 30, 2008 at 10:11 am
Wouldn't this work?
[font="Courier New"]SELECT
Fl.folderId
ISNULL(COUNT(EL.eventTargetId), 0) AS NumberofHits
FROM
folderList FL LEFT OUTER JOIN
na_production.dbo.Pagelist PL ON
FL.folderID = PL.folderID AND
PL.moduleID = 576456718 AND
PL.pageSequence > 0 AND
PL.hidden <> 1 LEFT OUTER JOIN
na_production.dbo.EventLog EL ON
EL.eventTargetId = PL.pageId
GROUP BY
FL.folderID
ORDER BY
NumberOfHits DESC [/font]
Your folder with the most hits would be a the top of the select list.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 30, 2008 at 10:39 am
If you really only want one row - use Jack's solution, but change it to state SELECT TOP 1, as in
SELECT Top 1
Fl.folderId
ISNULL(COUNT(EL.eventTargetId), 0) AS NumberofHits
FROM
folderList FL LEFT OUTER JOIN
na_production.dbo.Pagelist PL ON
FL.folderID = PL.folderID AND
PL.moduleID = 576456718 AND
PL.pageSequence > 0 AND
PL.hidden <> 1 LEFT OUTER JOIN
na_production.dbo.EventLog EL ON
EL.eventTargetId = PL.pageId
GROUP BY
FL.folderID
ORDER BY
NumberOfHits DESC
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 1, 2008 at 7:21 am
Jack, thanks for the post.
I tried your query, and it took about 2 minutes to run and returned 44,000 results(all zeroes).
My original query is
SELECT COUNT(EL.eventTargetId) AS NumberofHits
FROM na_production.dbo.EventLog EL
LEFT OUTER JOIN na_production.dbo.Pagelist PL ON EL.eventTargetId = PL.pageId
LEFT OUTER JOIN folderList FL ON FL.folderID = PL.folderID
WHERE PL.moduleID = 576456718
AND PL.pageSequence > 0
AND PL.hidden <> 1
GROUP BY EL.eventTargetId
It produces 12 results with NumberofHits as the column. Now I need to get the maximum value in the NumberofHits column.
I originally tried putting MAX(COUNT(EL.eventTargetId)), but that returned an error saying I can't put MAX and COUNT together.
Thanks! 🙂
May 1, 2008 at 7:55 am
I knew my query would return more results than yours, but it should have returned some data. The all 0's result means that there are records meeting the criteria (no records in that walk the chain from pages up to folders). I made the modifications intentionally based on your statement that your desired outcome was to get the number of hits per folder. Your query eliminated any folders with no hits and I rewrote it to include all records in FolderList. I also included folderID in the select list and group by since your original query does not seem to meet your desired results. In order to return the number of hits by folder you need to group on folder and include it in your select. Your original query gives you the count of each eventTargetID and, based on the information you have provided, I don't see a relationship between eventTargetId and folderId. It looks like your original query returns the number of hits per page in a Folder, not the number of hits per folder. You also do not return what the count applies to, it could be a count of anything.
If you post the DDL of your tables and some sample data I may be able to provide a better solution.
Is there the possibility of there being records in pageList with no folderID? If not, in my query, change the join from folderList to pageList to an Inner Join, that will definitely improve performance.
To get the max number of hits all you need to do is add the order by NumberOfHits Desc and that will have the top page as the first record.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 1, 2008 at 3:51 pm
Magy,
you're tryint to count number of hits ON WHAT?
On pages?
Then show it to you query. Put PageID into GROUP BY.
Also logic of your query is flipped over.
SELECT PL.pageId, COUNT(EL.eventTargetId) AS NumberofHits
FROM na_production.dbo.Pagelist PL
LEFT OUTER JOIN na_production.dbo.EventLog EL ON EL.eventTargetId = PL.pageId
LEFT OUTER JOIN folderList FL ON FL.folderID = PL.folderID
WHERE PL.moduleID = 576456718
AND PL.pageSequence > 0
AND PL.hidden <> 1
GROUP BY PL.pageId
_____________
Code for TallyGenerator
May 1, 2008 at 3:57 pm
Also don't see any reason for folderList to be mentioned in this query.
Unless you need to slow it down.
_____________
Code for TallyGenerator
July 29, 2010 at 5:16 am
Hi Magy, Matt, Sergiy and Jack
I am new to SqlServer.
I was also facing the same problem of finding the maximum count.
I dont know if this is the efficient way.. but I achieved finding Maximum count using this way.
Use this query to get the targetid with maximum number of hits..
SELECT EL.eventTargetId AS TargetWithMoreHits, COUNT(EL.eventTargetId) as NumberOfHits
FROM na_production.dbo.EventLog EL
LEFT OUTER JOIN na_production.dbo.Pagelist PL ON EL.eventTargetId = PL.pageId
LEFT OUTER JOIN folderList FL ON FL.folderID = PL.folderID
WHERE PL.moduleID = 576456718 AND PL.pageSequence > 0 AND PL.hidden <> 1
GROUP BY EL.eventTargetId
HAVING COUNT(EL.eventTargetId) = (SELECT TOP 1 COUNT(eventTargetId) from na_production.dbo.EventLog group by eventTargetId order by COUNT(eventTargetId) DESC)
If there is anyother way of doing it please share it. you can also optimize this I think...:-)
Please Comment on this.
Cheers
Mahendran
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply