2

I have 2 tables (it is only part of table columns that are needed for this query): 

Items: Id int, OwnerId int
ItemsRelations: RelationId int, ItemId Int (FK Items(Id))

For each OwnerId i need to calculate result of ratio function: Count of user elements that have links to ItemsRelationstable / count of all user elements.

I wrote this query, but it is slow and not optimal. Is there easier and faster method to do that?

SELECT OwnerId , (100 * SUM(HasRelation))/COUNT(Id) AS Ratio
FROM (
  SELECT 
    oi.OwnerId,
    oi.Id,
    (CASE WHEN SUM(ir.Id) > 0 THEN 1 ELSE 0 END) HasRelation
  FROM Items AS oi
  LEFT JOIN ItemsRelations AS ir ON ir.ItemId  = oi.Id
  GROUP BY oi.Id, oi.OwnerId) tempTab 
GROUP BY OwnerId
Marek Kwiendacz
  • 9,524
  • 14
  • 48
  • 72

2 Answers2

2
SELECT oi.OwnerId, Ratio = 1.0 * Count(ir.ItemId) / Count(*)
FROM Items AS oi
LEFT JOIN ItemsRelations AS ir ON ir.ItemId  = oi.Id
GROUP BY oi.OwnerId

Notes:

  • 1.0 is added to convert the expression into a float result
  • Count(ir.ItemId) skips NULLS, so it counts only those that have a relationship

If Items to ItemsRelations is one to many, then you may need to DISTINCT it

SELECT oi.OwnerId, Ratio = 1.0 * Count(DISTINCT ir.ItemId) / Count(DISTINCT oi.Id)
FROM Items AS oi
LEFT JOIN ItemsRelations AS ir ON ir.ItemId  = oi.Id
GROUP BY oi.OwnerId
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • Aaaa.. I've created first version and it results with wrong answers soI've made this nested version. But DISTINCT works like a charm. I din't know that I can use it there. Thanks. – Marek Kwiendacz Feb 17 '12 at 11:12
0

I think this should do the trick:

select i.OwnerId,
       case when count(i.Id) > 0 then count(r.Id)/count(i.Id) else 0 end as Ratio
from Items i
left join ItemsRelations r on r.ItemId = i.Id
group by i.Id, i.OwnerId
aF.
  • 64,980
  • 43
  • 135
  • 198