Click here to Skip to main content
13,150,344 members (30,139 online)
Rate this:
Please Sign up or sign in to vote.
I have two tables Company and CompanyLike



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 12-Feb-13 23:56pm
Updated 12-Feb-13 23:58pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Try below query:
--rating star
SET @cols = '[1],[2],[3],[4],[5]'
SELECT [CompanyID], @cols
PIVOT (COUNT([CompanyId]) FOR [RatingStar] IN (@cols))

CompanyID    1     2    3   4   5
5636    0    0     0    0    2
Ahsanashfaqattari.12 13-Feb-13 7:24am
I already have a solution with PIVOT but i don't want to use PIVOT
select *
from (
select as rate, as comp,
l.idComp as n
from compa c
cross join rates r
left join likes l
on l.rating =
and = l.idComp
) as src
count( n )
FOR rate IN ( [1], [2], [3], [4], [5] )
) as pvt ;
Maciej Los 13-Feb-13 7:44am
So, what are you excpecting from me?

2 stars???

I wan't waste my time next time...
Ahsanashfaqattari.12 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 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
Please Sign up or sign in to vote.

Solution 2

select RatingStar as RatingStar, 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 = c.Companyid
 Group By
 RatingStar ,com.Name ,

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web02 | 2.8.170924.2 | Last Updated 14 Feb 2013
Copyright © CodeProject, 1999-2017
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