4
Answers

Get the Average Score and display

Yong Kevin

Yong Kevin
4y
684
1
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: 
  1. SELECT s.Services_id, s.Service_Name,s.Service_Description,s.Price,AVG(r.Score)  
  2. FROM  Rating r   
  3. FULL OUTER JOIN Services s ON r.Services_Id = s.Services_Id      
  4. WHERE s.Service_Status = 'active'     
  5. GROUP BY s.Services_id,r.Services_Id,s.Service_Name,s.Service_Description,s.Price    
 
In my model: 
 
  1. public class Services  
  2.    {  
  3.        public int Services_id {get; set;}  
  4.        public string Service_Name { get; set; }  
  5.        public string Service_Description { get; set; }  
  6.        public Decimal Price { get; set; }  
  7.        public string Service_Status { get; set; }  
  8.        public int Service_Provider_Id { get; set; }  
  9.        public int Currency_Id { get; set; }  
  10.        public string Currency_Name { get; set; }  
  11.        public int Category_id { get; set; }  
  12.        public int Rating_Id { get; set; }  
  13.        public int Score { get; set; }  
  14.        public int Service_Score { get; set; }  
  15.    }  
In my view:
 
  1. @{  
  2.     Layout = "_Guest_NavBar";  
  3. }  
  4. @model List<Services>  
  5. <title>ServeTheWorld</title>  
  6. <link href="~/lib/datatables/css/jquery.dataTables.min.css" rel="stylesheet" />  
  7. <script src="~/lib/datatables/js/jquery.dataTables.min.js"></script>  
  8.   
  9. <script>  
  10.     $(document).ready(function () {  
  11.         $('#DataTable').DataTable({  
  12.             paging: true,  
  13.             ordering: true,  
  14.             searching: true,  
  15.             info: true,  
  16.             lengthMenu: [[6, 10, 20, -1], [6, 10, 20, "All"]]  
  17.         });  
  18.     });  
  19. </script>  
  20.   
  21. <h1>Services Available</h1>  
  22. <table id="DataTable" class="table-style table">  
  23.     <thead>  
  24.         @{  
  25.             var sno = 1;  
  26.         }  
  27.         <tr>  
  28.             <th>No.</th>  
  29.             <th>Service Name</th>  
  30.             <th>Service Description</th>  
  31.             <th>Service Price</th>  
  32.             <th>Ratings</th>  
  33.             <th>Actions</th>  
  34.         </tr>  
  35.     </thead>  
  36.     <tbody>  
  37.         @foreach (Services s in Model)  
  38.         {  
  39.             <tr>  
  40.                 <td>@s.Services_id</td>  
  41.                 <td>@s.Service_Name</td>  
  42.                 <td>@s.Service_Description</td>  
  43.                 <td>@s.Price</td>  
  44.                 <td>  
  45.                     @for (int i = 0; i < (Model.Sum(s=>s.Score)/Model.Count); i++)  
  46.                     {  
  47.                         <span class="fa fa-star" style="color:darkorange"></span>  
  48.                     }  
  49.   
  50.                     @for (int i = (Model.Sum(s => s.Score) / Model.Count); i < 5; i++)  
  51.                     {  
  52.                         <span class="fa fa-star-o" style="color:darkorange"></span>  
  53.                     }  
  54.                 </td>  
  55.                 <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>  
  56.             </tr>  
  57.         }  
  58.     </tbody>  
  59. </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 
 
Answers (4)
Next Recommended Forum