CODEWITHSUNDEEP
sql-server
Ashkan Mobayen Khiabani
Ashkan Mobayen Khiabani

Reputation: 34152

SQL Server : get count and sum of columns

I am designing a website that people post text messages. users can rate the messages (1 to 5). I'm recording this ratings into a table to:

  1. avoid multiple rating by one user for the same post
  2. to be able get the average rating of a post.

but I also the number of votes are important too. for example a post having 10 votes of 3 (makes 30) are valuable than the post with 2 votes of 5 (that makes 10). Now when I'm showing a page with 20 of this messages I want to show their rating too

This is my rating table:

create table ratings(
[id] int primary key identity(1,1),
[msgid] int,
[uid] int,
[rating] tinyint
)

I want to get to 20 messages from messages table with the sum of the ratings for each message and the count of ratings for each message.

select 
    msg.id, msg.text,
    sum(rate.rating), 
    count(rate.id) 
from 
    messages as msg 
inner join 
    ratings as rate on msg.id = rate.msgid 
group by 
    msg.id

can any one please help me with the SQL query code?

Upvotes: 0

Views: 594

Answers (3)

Brock Adams
Brock Adams

Reputation: 93453

You need to coordinate the group by with the select. Also, account for zero ratings, so use a left join:

SELECT
    msg.id,
    msg.text,
    SUM (COALESCE (rate.rating, 0) )   AS ratingSum,
    COUNT (COALESCE (rate.id, 0) )     AS ratingCnt
FROM
    messages as msg
LEFT JOIN
    ratings rate ON msg.id = rate.msgid
GROUP BY
    msg.id,
    msg.text

Upvotes: 2

Piotr Sobiegraj
Piotr Sobiegraj

Reputation: 1783

You cannot select columns which haven't been used in group by clause.

Try:

select 
    msg.id, msg.text,
    sum(rate.rating), 
    count(rate.id) 
from 
    messages as msg 
inner join 
    ratings as rate on msg.id = rate.msgid 
group by 
    msg.id, msg.text

Upvotes: 2

RoninEngineer
RoninEngineer

Reputation: 396

I think this will work.

select msgid,
count(msgid) MsgCount,
       sum(rating)  RatingSum
from ratings
where msgid = [some msgid]
group by msgid, rating

This should work on most ANSI standard transact sql platforms. You may have to modify it for your particular database.

Thanks, --James Walsh Jr. --Developer at Large

Upvotes: 0

Related Questions