CODEWITHSUNDEEP
mysqlsql
Burak Karakuş
Burak Karakuş

Reputation: 1410

SQL - Trying to find rate of 1's over 0's after group by?

I have two tables like following:

CouponTable

CouponOwner

Here is the scenario: I need to calculate the rate of IsAvailable(1 or 0) for each Coupon Owner? I couldn't find how I can first group it by COwners and then find the rate for each of them.

I tried this below sql code, which is specific for only on Owner and this works well, but I shouldn't be giving Id's of each Owner, I need a more generic way of doing it.

SELECT 
    C.COwnerId,
    O.COwnerName,
    (select count(*) from CouponTable where COwnerId = 105 and IsAvailable = 1) * 100 /(select count(*) from CouponTable where COwnerId = 105) as Rate
FROM
    CouponTable AS C
        JOIN
    CouponOwner AS O ON C.COwnerId = O.COwnerId where C.COwnerId = 105
GROUP BY C.COwnerId
ORDER BY Rate DESC;

This returns following:

105, Gareth Wilson, 21.9229

Upvotes: 0

Views: 49

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use a correlated subquery:

SELECT 
    C.COwnerId,
    O.COwnerName,
    (SELECT COUNT(*) 
     FROM CouponTable 
     WHERE COwnerId = c.COwnerId AND IsAvailable = 1) * 100 /
    (SELECT count(*) 
     FROM CouponTable 
     WHERE COwnerId = c.COwnerId) AS Rate
FROM
    CouponTable AS C
INNER JOIN
    CouponOwner AS O ON C.COwnerId = O.COwnerId 
GROUP BY C.COwnerId
ORDER BY Rate DESC;

This way you just have to substitute the specific COwnerId value, i.e. 105, with the value of the field coming from the outer query, i.e. c.COwnerId.

Demo here

Upvotes: 1

fthiella
fthiella

Reputation: 49049

You can Join the CouponTable with the CouponOwner table, and use a GROUP BY query with SUM and COUNT:

SELECT
  ID,
  CouponOwner,
  (SUM(IsAvailable)/COUNT(*))*100 As Rate
FROM
  CouponTable ct INNER JOIN CouponOwner co
  ON ct.CouponOwnner=co.CoOwnerID
GROUP BY
  ID,
  CouponOwner
ORDER BY
  (SUM(IsAvailable)/COUNT(*))*100 DESC

SUM(IsAvailabe) is the number of coupons available for the owner, and COUNT(*) is the number of all coupons for the owner.

Upvotes: 0

Related Questions