Add rows to final select if null query sql

Question

i want show percent rating product reviews table order by rate number.

for example:

5: 71%

4: 0%

3: 0%

2: 28%

1: 0%

maybe any rate number Not exist in product reviews table.(in above example rate 4,3,1 not exist in my table)

datas inserted in my table at above example is:

Id      CustomerId      ProductId      Rating
------- --------------- -------------- --------
39      14              57             2
42      18              57             5
56      19              57             5

my query for show percent is:

SELECT
      pr.ProductId ,
      pr.Rating,
      percentage = AVG(pr.Rating) * 100 / SUM(AVG(pr.Rating)) OVER (PARTITION BY pr.ProductId)
    FROM ProductReview pr  
    WHERE pr.ProductId = 57
    GROUP BY
      pr.ProductId,
      pr.Rating
    ORDER BY pr.Rating DESC

And result my query is:

ProductId   Rating      percentage
----------- ----------- -----------
57          5           71
57          2           28

but i don't how to show other rate number if not exist as zero percent.

thanks to all.

You are filtering on productid = 57. So you will got only product id with 57 — user8608099
– user8608099, Commented Sep 23, 2017 at 10:52

Tim Biegeleisen · Accepted Answer · 2017-09-23 10:58:38Z

If your raw data does not have ratings for every product, then you will have to somehow introduce this information into your query. One option is the calendar table approach (so named because it is used to cover missing dates in a data set). In the first CTE below, I generate all ratings, 1 through 5, for each product in your ProductReview table. Then, I left join this to your original query, matching each product/rating to each point of data in your original query. In case no match be possible, then we display the percentage as zero.

WITH cte AS (
    SELECT t1.ProductId, t2.Rating
    FROM (SELECT DISTINCT ProductId FROM ProductReview) t1
    CROSS JOIN
    (
        SELECT 1 AS Rating UNION ALL
        SELECT 2 UNION ALL
        SELECT 3 UNION ALL
        SELECT 4 UNION ALL
        SELECT 5
    ) t2
),
yourQuery AS (
    SELECT
        ProductId,
        Rating,
        AVG(pr.Rating) * 100 /
            SUM(AVG(pr.Rating)) OVER (PARTITION BY pr.ProductId) AS percentage
    FROM ProductReview  
    WHERE ProductId = 57
    GROUP BY ProductId, Rating
)

SELECT
    t1.ProductId,
    t1.Rating,
    COALESCE(t2.percentage, 0) AS percentage
FROM cte t1
LEFT JOIN yourQuery t2
    ON t1.ProductId = t2.ProductId AND
       t1.Rating    = r2.Rating
ORDER BY
    t1.ProductId,
    t1.Rating;

Collectives™ on Stack Overflow

Add rows to final select if null query sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related