 |  |  |  |  |  |  |  |
SQL SERVER 2000 Selecting a record based on an aggregate function
Please support our MS SQL advertiser: Intel Parallel Studio Home
 |
•
•
Join Date: Mar 2004
Posts: 634
Reputation: 
Solved Threads: 7
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.
•
•
•
•
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
Select Col1, Col2, Col3, Sum(Col4)
from sf_articles
group by Col1, Col2, Col3
•
•
Join Date: Mar 2004
Posts: 634
Reputation:
Solved Threads: 7
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
Posts: 634
Reputation:
Solved Threads: 7
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.
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.
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
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
•
•
•
•
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
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. :-|
•
•
•
•
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
Alaln
•
•
Join Date: Jun 2007
Posts: 95
Reputation:
Solved Threads: 9
Junior Poster in Training
Thanks a lot, your example is very useful to my requrirements
Shailaja
Shailaja
•
•
•
•
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
|
Other Threads in the MS SQL Forum
- Previous Thread: Mysql -> MS SQL Inserts
- Next Thread: Getting ID from reference table before executing Bulk insert
| Thread Tools | Search this Thread |
You need to join our community in order to build your list of favorite forums. You can visit the forum index for a listing of all forums.
Related Forum Features
Latest MS SQL Posts
- Today's Posts
- Unanswered Threads
