sql - SQlite fill empty cells

Question

I am writing this query to calculate.

SELECT
Date,
SUM(CASE WHEN (Terms="Sandwich") THEN Stars ELSE 0 END) / SUM(CASE WHEN (Terms="Sandwich") THEN 1 ELSE 0 END) AS Sandwich,
SUM(CASE WHEN (Terms="Chicken") THEN Stars ELSE 0 END) / SUM(CASE WHEN (Terms="Chicken") THEN 1 ELSE 0 END) AS Chicken,
SUM(CASE WHEN (Terms="Meat") THEN Stars ELSE 0 END) / SUM(CASE WHEN (Terms="Meat") THEN 1 ELSE 0 END) AS Meat,
SUM(CASE WHEN (Terms="Others") THEN Stars ELSE 0 END) / SUM(CASE WHEN (Terms="Others") THEN 1 ELSE 0 END) AS Others
FROM __table__
GROUP BY Date

Original Data is this.

This is the result of query.

/ is used for int division so why the result is in float. Second issue is some times the count and sum of terms is 0 so 0/0 is undefined. I want to fill those cells with 0. Please update query.

question from:https://stackoverflow.com/questions/65624239/sqlite-fill-empty-cells

Answer 1

You can do it with conditional aggregation and COALESCE() so that you get 0 instead of NULL:

SELECT Date,
       COALESCE(AVG(CASE WHEN Terms = 'Sandwich' THEN Stars END), 0) Sandwich,
       COALESCE(AVG(CASE WHEN Terms = 'Chicken' THEN Stars END), 0) Chicken,
       COALESCE(AVG(CASE WHEN Terms = 'Meat' THEN Stars END), 0) Meat,
       COALESCE(AVG(CASE WHEN Terms = 'Others' THEN Stars END), 0) Others
FROM __table__
GROUP BY Date;

AVG() returns a float.
If you want the result as integers you can use:

CAST(COALESCE(AVG(CASE WHEN Terms = 'Sandwich' THEN Stars END), 0) AS INTEGER)