Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
I have two tables Company and CompanyLike
 
CompanyLike
 
Id
RatingStar
Count
Companyid
Company and CompanyLike have 1 to Many relation . One company can have many likes
 
Company Like Table Will Look Like this
 
+----------------------------+
| ID  RatingStar   Companyid |
+----------------------------+
| 1      5           5636    |
| 2      5           5636    |
| 3      1           101     |
| 4      2            959    |
+----------------------------+
Now i want to get count of rating star that how many times RatingStar is 5 , and how many time its 2 against every Companyid
 
I want my result like this
 
+-------------------------------------------------------------------------------------------+
| ID     Companyid    RatingStar1   RatingStar2   RatingStar3   RatingStar4   RatingStar5   |
+-------------------------------------------------------------------------------------------+
| 1       5636               0           0               0          0             2         |
| 1       101                0           0               0          0             0         |
| 1       959                1           1               0          0             0         |
+-------------------------------------------------------------------------------------------+
 
How to do this ?
 
[edit]Code block added - OriginalGriff[/edit]
Posted 13-Feb-13 0:56am
Edited 13-Feb-13 0:58am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try below query:
DECLARE @cols NVARCHAR(200)
 
--rating star
SET @cols = '[1],[2],[3],[4],[5]'
 
SELECT [CompanyID], @cols
FROM (SELECT * FROM Company) AS DT
PIVOT (COUNT([CompanyId]) FOR [RatingStar] IN (@cols))
 
Result:
CompanyID    1     2    3   4   5
5636    0    0     0    0    2
...
  Permalink  
Comments
Ahsanashfaqattari.12 at 13-Feb-13 7:24am
   
I already have a solution with PIVOT but i don't want to use PIVOT
select *
from (
select r.id as rate,
c.id as comp,
l.idComp as n
from compa c
cross join rates r
left join likes l
on l.rating = r.id
and c.id = l.idComp
) as src
PIVOT
(
count( n )
FOR rate IN ( [1], [2], [3], [4], [5] )
) as pvt ;
Maciej Los at 13-Feb-13 7:44am
   
So, what are you excpecting from me?
 
2 stars???
 
I wan't waste my time next time...
Ahsanashfaqattari.12 at 14-Feb-13 0:31am
   
If u are working on stars then look again u got 5 now , and i mean to say that i don't want to use PIVOT , you can do anyway as u like but except PIVOT and that was my little request . Please don't mind
Maciej Los at 14-Feb-13 2:58am
   
I'm not doing it for "stars"... I was wondering why your opinion (via voting system) is so bad/poor. That's why i'm asking: why 2 stars? I need to know what i'm doing wrong to improve my answer, but i need to know the reasons of poor mark.
 
Remember, the answer is as good as a question. I don't see the (in your post) the question: How to achieve desired result without using pivot tables? You question is: I want my result like this... How to do this? without the reservation that "no pivot tables!"
 
Finally, can you tell me what do you want to do?
 
If you want to achieve desired result without pivot tables, i'm answering you: it's possible, but it's more complicated. Do you want to know another (more complicated) solution?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

select RatingStar as RatingStar,com.id as Companyid  ,com.Name as
         CompanyName ,
 
 Count(RatingStar) as Count,MIN(c.ID) as Id
     from CompanyReviewLike as c
   inner join Company as com on com.id = c.Companyid
 Group By
 RatingStar ,com.Name , com.id
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 350
1 Jochen Arndt 150
2 PIEBALDconsult 110
3 Richard MacCutchan 105
4 Sergey Alexandrovich Kryukov 104
0 OriginalGriff 6,055
1 DamithSL 4,621
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,500
4 Sergey Alexandrovich Kryukov 3,294


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 14 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100