Originally Posted by Slade
Hi guys here is my working sql query
Select MAX(Hits)'Hits', SUM(Hits)'Total', UserID
From sf_articles
Group By UserID
My problem is, when I want to select other columns as well as the current ones. It gives me a lovely error:
Server: Msg 8120, Level 16, State 1, Line 1
Column 'sf_articles.ArtID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Basically what I want to do is get those columns, and get the info from the records that contain the maximum hits.
For example. I have two records, one with 2 hits, the other with 5. I want to get all the info from the one with 5 hits. There will be an end result of about 7 records at the end since there are 7 users.
PLEASE HELP! I AM TEARING MY HAIR OUT!
Cheers,
Slade
Sorry I'm late in this, but somebody else might benefit from this...
I think this might be what you're looking for:
SELECT SF1.UserID, <other SF1 fields>, SF1.Hits, SUM(SF1.Hits) Total
FROM sf_articles SF1
INNER JOIN sf_articles SF2 ON SF1.UserID = SF2.UserID
GROUP BY SF1.UserID
HAVING SF1.Hits = MAX(SF2.Hits)
In the above statement, I'm not certain on whether it's SUM(SF
1.Hits) or SUM(SF
2.Hits). I'd try them both and verify the SUM matches the results of this statement:
SELECT UserID, SUM(Hits) Total
FROM sf_articles
GROUP BY UserID
I hope that helped, or was even remotely close to what you needed. :-|
