TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Yong Kevin
NA
35
2.3k
Get the Average Score and display
Jan 29 2021 11:08 AM
Hi everyone,
I would like to ask how can I display the service ratings in view.
For now, I have put in one SQL Statement in my controller:
SELECT s.Services_id, s.Service_Name,s.Service_Description,s.Price,AVG(r.Score)
FROM Rating r
FULL OUTER JOIN Services s ON r.Services_Id = s.Services_Id
WHERE s.Service_Status =
'active'
GROUP BY s.Services_id,r.Services_Id,s.Service_Name,s.Service_Description,s.Price
In my model:
public
class
Services
{
public
int
Services_id {get; set;}
public
string Service_Name { get; set; }
public
string Service_Description { get; set; }
public
Decimal Price { get; set; }
public
string Service_Status { get; set; }
public
int
Service_Provider_Id { get; set; }
public
int
Currency_Id { get; set; }
public
string Currency_Name { get; set; }
public
int
Category_id { get; set; }
public
int
Rating_Id { get; set; }
public
int
Score { get; set; }
public
int
Service_Score { get; set; }
}
In my view:
@{
Layout =
"_Guest_NavBar"
;
}
@model List<Services>
<title>ServeTheWorld</title>
<link href=
"~/lib/datatables/css/jquery.dataTables.min.css"
rel=
"stylesheet"
/>
<script src=
"~/lib/datatables/js/jquery.dataTables.min.js"
></script>
<script>
$(document).ready(function () {
$(
'#DataTable'
).DataTable({
paging:
true
,
ordering:
true
,
searching:
true
,
info:
true
,
lengthMenu: [[6, 10, 20, -1], [6, 10, 20,
"All"
]]
});
});
</script>
<h1>Services Available</h1>
<table id=
"DataTable"
class
=
"table-style table"
>
<thead>
@{
var sno = 1;
}
<tr>
<th>No.</th>
<th>Service Name</th>
<th>Service Description</th>
<th>Service Price</th>
<th>Ratings</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (Services s in Model)
{
<tr>
<td>@s.Services_id</td>
<td>@s.Service_Name</td>
<td>@s.Service_Description</td>
<td>@s.Price</td>
<td>
@
for
(
int
i = 0; i < (Model.Sum(s=>s.Score)/Model.Count); i++)
{
<span
class
=
"fa fa-star"
style=
"color:darkorange"
></span>
}
@
for
(
int
i = (Model.Sum(s => s.Score) / Model.Count); i < 5; i++)
{
<span
class
=
"fa fa-star-o"
style=
"color:darkorange"
></span>
}
</td>
<td><a asp-controller=
"Homepage"
asp-action=
"ViewServices"
asp-route-id=
"@s.Services_id"
><i
class
=
"fa fa-info-circle"
style=
"font-size:30px"
></i></a></td>
</tr>
}
</tbody>
</table>
Now, I want to display all the services and their rating score on the webpage.
When I run once with the SQL Statement that I put into my controller action, it does not show the rating on the webpage.
For example:
In my database, I have record with the Services_Id = 1 and this id has three rating scores(4,2,4) stored inside the rating table:
So I want to use AVG to calculate the average rating score and it has an average score of 3:
When I run and on the webpage does not display 3 stars which is the average score for Services_Id = 3:
How can I achieve this
Please help
Thank you
Reply
Answers (
4
)
Convert string array to dictionary taking first 3 chatacters as key.
How does sqlcommand builder work?