•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 455,970 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,773 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser: Programming Forums
Views: 56909 | Replies: 8
 |
•
•
Join Date: Mar 2004
Location: Brisbane
Posts: 632
Reputation: 
Rep Power: 7
Solved Threads: 6
Practically a Master Poster
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
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
Formerly known as Slade.
•
•
Join Date: Aug 2004
Location: Huntsville, AL
Posts: 1
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Re: SQL SERVER 2000 Selecting a record based on an aggregate function •
•
•
•
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
•
•
Join Date: Mar 2004
Location: Brisbane
Posts: 632
Reputation:
Rep Power: 7
Solved Threads: 6
Practically a Master Poster
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?
. So is there any way to select the record with the highest number of hits for each user? Formerly known as Slade.
•
•
Join Date: Mar 2004
Location: Brisbane
Posts: 632
Reputation:
Rep Power: 7
Solved Threads: 6
Practically a Master Poster
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.
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.
Formerly known as Slade.
•
•
Join Date: Feb 2005
Posts: 2
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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.
•
•
Join Date: Nov 2005
Posts: 1
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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
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
•
•
Join Date: Nov 2005
Location: Dubai,UAE
Posts: 36
Reputation:
Rep Power: 4
Solved Threads: 0
Light Poster
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
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
•
•
Join Date: Mar 2006
Posts: 1
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
•
•
•
•
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. :-|
•
•
Join Date: Sep 2007
Posts: 2
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
•
•
•
•
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
|
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
•
•
•
•
ad advertising ajax architecture asp backup beta business centro crash daniweb database dell developer development doubleclick enterprise google hacker internet linux longhorn mail microsoft mmorpg msdn news novell office open qmail red hat rhel security server smtp software source sql survey suse vista windows windows server
- Freelance Web designer PHP, MySQL, ASP, SQL Server 2000 (Post your Resume)
- Ms Sql Server 2000 (MS SQL)
Other Threads in the MS SQL Forum
- Previous Thread: ms sql2005:error msg
- Next Thread: Query Help
