CODEWITHSUNDEEP
sqlgoogle-bigqueryaggregation
Chandan Bhattad
Chandan Bhattad

Reputation: 371

SQL - Group and Aggregation to calculate ratio with respect to total

My table looks like below:

product offer

Product1 O1

Product1 O1

Product1 O2

Product2 O3

Product2 O4

Where product is a product listed on website and offer is the offer for the product viewed by customer.

What I want is, a query to show percentage of each offer view with respect to total offer views for that product. So the result should look like below:

Product Offer ViewPercentage

Product1 O1 67%

Product1 O2 33%

Product2 O3 50%

Product2 O4 50%

Upvotes: 3

Views: 2229

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Use aggregation with window functions:

SELECT product, offer,
       COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY product) as ViewRatio
FROM offers
GROUP BY product, offer;

Note: This produces a ratio between 0 and 1 rather than a value between 0 and 100. You can multiply by 100 if you really want. And even convert to a string with % if that is desired.

Upvotes: 3

XING
XING

Reputation: 9886

In Oracle 11g onwards RATIO_TO_REPORT is an analytic function which can be helpful here.

See here.

SELECT product,
  offer,
  round(RATIO_TO_REPORT(cnt) over(partition by  product ) * 100 ) AS "%" 
FROM 
 ( SELECT product,
          offer,
           COUNT(offer) cnt 
   FROM tab
    GROUP BY product, offer);

Upvotes: 1

Nick
Nick

Reputation: 147196

You can use window functions to compute the view percentage for each offer:

SELECT DISTINCT product, offer,
     100.0 * (COUNT(*) OVER (PARTITION BY product, offer)) / (COUNT(*) OVER (PARTITION BY product)) AS ViewPercentage
FROM offers

Upvotes: 2

Related Questions