Ad:

MS SQL Discussion Thread
Unsolved
Views: 87963

Aug 24th, 2004

SQL SERVER 2000 Selecting a record based on an aggregate function

Expand Post »

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

Slade

Reputation Points: 115

Solved Threads: 7

Practically a Master Poster

Offline

633 posts
since Mar 2004

Permalink

Aug 24th, 2004

Re: SQL SERVER 2000 Selecting a record based on an aggregate function

Quote 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

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

DawnLane

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Aug 2004

Permalink

Aug 24th, 2004

Re: SQL SERVER 2000 Selecting a record based on an aggregate function

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

Reputation Points: 115

Solved Threads: 7

Practically a Master Poster

Offline

633 posts
since Mar 2004

Permalink

Aug 25th, 2004

Re: SQL SERVER 2000 Selecting a record based on an aggregate function

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.

Slade

Reputation Points: 115

Solved Threads: 7

Practically a Master Poster

Offline

633 posts
since Mar 2004

Permalink

Feb 23rd, 2005

Re: SQL SERVER 2000 Selecting a record based on an aggregate function

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.

capt_phill

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Feb 2005

Permalink

Nov 12th, 2005

Re: SQL SERVER 2000 Selecting a record based on an aggregate function

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

Mike_Meg

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Nov 2005

Permalink

Nov 17th, 2005

Re: SQL SERVER 2000 Selecting a record based on an aggregate function

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

noman78

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

40 posts
since Nov 2005

Permalink

Mar 17th, 2006

Re: SQL SERVER 2000 Selecting a record based on an aggregate function

Quote 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(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. :-|

erdany

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Mar 2006

Permalink

Sep 18th, 2007

Re: SQL SERVER 2000 Selecting a record based on an aggregate function

Click to Expand / Collapse Quote originally posted by DawnLane ...

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

AlanJ1

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Sep 2007

Permalink

Aug 13th, 2009

Re: SQL SERVER 2000 Selecting a record based on an aggregate function

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

Shailaja

Click to Expand / Collapse Quote originally posted by 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

manoshailu

Reputation Points: 34

Solved Threads: 10

Junior Poster

Offline

105 posts
since Jun 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Mysql -> MS SQL Inserts

Next Thread in MS SQL Forum Timeline: Getting ID from reference table before executing Bulk insert

DaniWeb Message

Cancel Changes

User Name
Password