SQL SERVER 2000 Selecting a record based on an aggregate function

[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

[DawnLane]

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

Every column in your select statement that is not used in an aggregate function MUST be included in the GROUP BY clause. For example,
Select Col1, Col2, Col3, Sum(Col4)
from sf_articles
group by Col1, Col2, Col3

[Slade]

But the only problem is, I only want to select the records which have the highest hits for each user. If I group by userid, then it singles them down a record from each user. But if I group by userid and artid, then it shows all the records :(. So is there any way to select the record with the highest number of hits for each user?

[Slade]

Hmm well, this is what I have so far and it works.

CREATE procedure sf_GetMemberInfo
@ArtID int output,
@Title nvarchar(200) output,
@ArticleHits int output,
@UserID nvarchar(20),
@Info nvarchar(1000) output,
@Notes nvarchar(3000) output,
@Favurl nvarchar(60) output,
@FavText nvarchar(200) output,
@PageHits int output
AS
SELECT
@ArtID = ArtID,
@Title = Title,
@ArticleHits = sf_articles.Hits,
@UserID = sf_articles.UserID,
@Info = Info ,
@Notes = Notes,
@Favurl = Favurl,
@FavText = FavText,
@PageHits = sf_Members.Hits + 1

FROM sf_articles, sf_Members
WHERE sf_articles.UserID = @UserID
AND
sf_articles.Hits = (Select MAX(Hits) from sf_articles where UserID = @UserID)
AND
sf_articles.UserID = sf_Members.UserID
UPDATE sf_Members
SET
Hits = Hits + 1
WHERE UserID = @UserID
GO

I guess I"ll have to make an additional query to get the total hits and the total number of records. The above is to work in unison with my asp .net app. I will post a simplified version later.

[capt_phill]

We ran into the same sort of problem with the aggregate functions and group by clause. By adding all non-aggregates in the group by it can changes the way we want to display the data..annoying.

[Mike_Meg]

First I am new to all this but would the following work for you?

The first query gives the same error as you were stating but if you replace the group by with a sub query and a where clause that references the group by field you can get all the columns
you want without any erros.
--This quer errors out
SELECT
AccountType,
[Description],
Rate,
Balance,
sum([BALANCE])'TOTAL'
FROM Account
GROUP BY AccountType

-- This one returns values you want
SELECT
acct.AccountType,
[Description],
Rate,
Balance,
(SELECT sum(Balance) FROM Account WHERE AccountType=Acct.AccountType)'TOTAL'
FROM Account AS acct
ORDER BY Balance DESC

[noman78]

hello
i got the same problem when i convert Access DB to SQL, in Access we have first function to do the job, in sql only option is to use MAX(fldname) for all those u want to display only, like this only one rec will be filter, in the case of string the last sorted
so far this is the only solution if u got anyother plz let me know

[erdany]

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(SF1.Hits) or SUM(SF2.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. :-|

[AlanJ1]

Every column in your select statement that is not used in an aggregate function MUST be included in the GROUP BY clause. For example,
Select Col1, Col2, Col3, Sum(Col4)
from sf_articles
group by Col1, Col2, Col3

I too was confused by this error message, and found your post. It was succinct, and helpful. Thank you.

Alaln

[manoshailu]

Thanks a lot, your example is very useful to my requrirements

Shailaja :)

First I am new to all this but would the following work for you?

The first query gives the same error as you were stating but if you replace the group by with a sub query and a where clause that references the group by field you can get all the columns
you want without any erros.
--This quer errors out
SELECT
AccountType,
[Description],
Rate,
Balance,
sum([BALANCE])'TOTAL'
FROM Account
GROUP BY AccountType

-- This one returns values you want
SELECT
acct.AccountType,
[Description],
Rate,
Balance,
(SELECT sum(Balance) FROM Account WHERE AccountType=Acct.AccountType)'TOTAL'
FROM Account AS acct
ORDER BY Balance DESC