CODEWITHSUNDEEP
c#asp.netsql
user3562319
user3562319

Reputation: 3

How do you sum a column of a database to display total ratings in ASP.NET?

I'm trying to make a ratings system. I'm using visual studio. The database has 2 tables, one table for all the music tracks, and another table containing the ratings, and is hooked up with an FK.

Here's my tables and columns http://i.gyazo.com/fc5d042749c8c04fb2b9aa2b64831b0a.png

This is my current attempt and it's giving me an error

SELECT DISTINCT Track.TrackId, SUM(Ratings.rating) AS average, Track.Name, Ratings.trackid
FROM Track
INNER JOIN Ratings
ON Track.TrackId = Ratings.trackid

Msg 8120, Level 16, State 1, Line 1 Column 'Track.TrackId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 0

Views: 669

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

If you want to use aggregation functions, then you generally want a group by. Without the group by, the query will return only one row.

SELECT t.TrackId, SUM(r.rating) AS average, t.Name
FROM Track t INNER JOIN
     Ratings r
     ON t.TrackId = r.trackid
GROUP BY t.TrackId, t.name;

In addition, I made the following changes:

  • Removed distinct from the select. This is almost never needed for an aggregation query.
  • Added table aliases that are abbreviations of the table names, to make the query more readable.
  • Removed Ratings.TrackId from the select statement. It is redundant, because it is the same as Track.TrackId.
  • Added a group by statement.

Upvotes: 0

Dalorzo
Dalorzo

Reputation: 20014

Every time you are using either a sum, avr, min or max, etc. functions you have to use Group by that is the law..

What you need to do first is SUM the ratings by track and then joining those later with your track table, something like this:

SELECT T.TrackId, SUM(R.rating) AS average, T.Name
FROM Track T
INNER JOIN 
(SELECT TrackId, SUM(Ratings.rating) AS average
FROM Ratings Group By TrackId ) R
ON T.TrackId = r.TrackId

Upvotes: 1

Chris F Carroll
Chris F Carroll

Reputation: 12370

Don't use distinct. It doesn't do the same thing as Group By.

In SQL-think, what you're trying to do is group all the rows by Trackid, and average the rating in each group which you do like this:

SELECT Track.TrackId, AVG(1.0000 * Ratings.rating)  AS average
FROM Track
JOIN Ratings ON Track.TrackId = Ratings.trackid
Group By Track.TrackId

But, you're also trying to pick up the Name at the same time. Doing that at that same time as a group by isn't as straightforward in SQL as you might wish. A 'correct' way is something like:

SELECT 
    Track.TrackId, 
    Average, 
    Name
FROM Track
INNER JOIN (
    SELECT TrackId, AVG(1.0000 * Ratings.rating)  AS average
    FROM Ratings 
    Group By TrackId
    ) R
ON Track.TrackId = R.trackid

Upvotes: 0

Related Questions