Question 281019 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – What is causing the query to produce the wrong percentage change result?

Background: The from_current is a year (-2 is 2 years ago and -1 is 1 year ago). The following query is suppose to show the items purchased in each category grouped by 2 years ago and 1 year ago. The 3rd row is suppose to show the change between 2 years ago and 1 year ago. Instead of showing the real percentage change as a result, the query is showing 0.00 instead. What some reasons for this error?

Here is my query:

WITH YearlyCounts AS (
    SELECT
        from_current,
        COUNT(CASE WHEN accessories > 0 THEN 1 ELSE NULL END) as accessories,
        COUNT(CASE WHEN camping > 0 THEN 1 ELSE NULL END) as camping,
        COUNT(CASE WHEN footwear > 0 THEN 1 ELSE NULL END) as footwear,
        COUNT(CASE WHEN apparel > 0 THEN 1 ELSE NULL END) as apparel,
        COUNT(CASE WHEN kids_clothes > 0 THEN 1 ELSE NULL END) as kids_clothes,
        COUNT(CASE WHEN biking > 0 THEN 1 ELSE NULL END) as biking,
        COUNT(CASE WHEN ski_snow_gear > 0 THEN 1 ELSE NULL END) as ski_snow_gear,
        COUNT(CASE WHEN fishing > 0 THEN 1 ELSE NULL END) as fishing
    FROM tenpeaks_sales
    WHERE loyalty = 't'
    GROUP BY from_current
)
SELECT 
from_current,
accessories,
camping,
footwear,
apparel,
kids_clothes,
biking,
ski_snow_gear,
fishing
FROM YearlyCounts

UNION ALL

SELECT
-1 AS from_current,
    ROUND(((S1.accessories - S2.accessories)/S2.accessories)*100, 2) AS access_change_1_year_ago,
      ROUND(((S1.camping - S2.camping)/S2.camping)*100, 2) AS camping_change_1_year_ago,
      ROUND(((S1.footwear - S2.footwear)/S2.footwear)*100, 2) AS footwear_change_1_year_ago,
      ROUND(((S1.apparel - S2.apparel)/S2.apparel)*100, 2) AS apparel_change_1_year_ago,
      ROUND(((S1.kids_clothes - S2.kids_clothes)/S2.kids_clothes)*100, 2) AS kids_clothes_change_1_year_ago,
      ROUND(((S1.biking - S2.biking)/S2.biking)*100, 2) AS biking_change_1_year_ago,
      ROUND(((S1.ski_snow_gear - S2.ski_snow_gear)/S2.ski_snow_gear)*100, 2) AS ski_snow_gear_change_1_year_ago,
      ROUND(((S1.fishing - S2.fishing)/S2.fishing)*100, 2) AS fishing_change_1_year_ago
FROM YearlyCounts S1
LEFT JOIN YearlyCounts S2 ON S1.from_current = S2.from_current + 1
WHERE S1.from_current = -1
ORDER BY from_current ASC;

This is the result of my query:
Items sold in each category

I would like to 3rd row to actually show the calculated percentage change for each of the categories, but instead it is showing 0.00. Please help. Thank you so much in advance.

Tags:

2

Answers


  1. 0

    Too much code to bother reading (in the future, narrow down the issue more before posting), but the problem is almost certainly integer division.

    When you divide two integers, the result is also an integer with any decimal portion truncated, meaning percentages calculations with integer operands tend to end up showing 0 unless you make the effort to cast one side to a floating point type (multiplying by 1.0 will usually do it).

    Login or Signup to reply.
  2. 0

    COUNT(*) returns a bigint and a bigint divided by another bigint returns a bigint. That will never be fractional, so no decimals. Cast your data to numeric and you will be fine:

    WITH
    yearlycounts AS (SELECT from_current
                          , COUNT(CASE WHEN accessories > 0 THEN 1 ELSE NULL END)   AS accessories
                          , COUNT(CASE WHEN camping > 0 THEN 1 ELSE NULL END)       AS camping
                          , COUNT(CASE WHEN footwear > 0 THEN 1 ELSE NULL END)      AS footwear
                          , COUNT(CASE WHEN apparel > 0 THEN 1 ELSE NULL END)       AS apparel
                          , COUNT(CASE WHEN kids_clothes > 0 THEN 1 ELSE NULL END)  AS kids_clothes
                          , COUNT(CASE WHEN biking > 0 THEN 1 ELSE NULL END)        AS biking
                          , COUNT(CASE WHEN ski_snow_gear > 0 THEN 1 ELSE NULL END) AS ski_snow_gear
                          , COUNT(CASE WHEN fishing > 0 THEN 1 ELSE NULL END)       AS fishing
                     FROM tenpeaks_sales
                     WHERE loyalty = 't'
                     GROUP BY from_current)
SELECT from_current
     , accessories
     , camping
     , footwear
     , apparel
     , kids_clothes
     , biking
     , ski_snow_gear
     , fishing
FROM yearlycounts
UNION ALL
SELECT -1                                                                         AS from_current
     , ROUND(((s1.accessories - s2.accessories) / s2.accessories::numeric) * 100, 2)       AS access_change_1_year_ago
     , ROUND(((s1.camping - s2.camping) / s2.camping::numeric) * 100, 2)                   AS camping_change_1_year_ago
     , ROUND(((s1.footwear - s2.footwear) / s2.footwear::numeric) * 100, 2)                AS footwear_change_1_year_ago
     , ROUND(((s1.apparel - s2.apparel) / s2.apparel::numeric) * 100, 2)                   AS apparel_change_1_year_ago
     , ROUND(((s1.kids_clothes - s2.kids_clothes::numeric) / s2.kids_clothes) * 100, 2)    AS kids_clothes_change_1_year_ago
     , ROUND(((s1.biking - s2.biking) / s2.biking::numeric) * 100, 2)                      AS biking_change_1_year_ago
     , ROUND(((s1.ski_snow_gear - s2.ski_snow_gear::numeric) / s2.ski_snow_gear) * 100, 2) AS ski_snow_gear_change_1_year_ago
     , ROUND(((s1.fishing - s2.fishing) / s2.fishing::numeric) * 100, 2)                   AS fishing_change_1_year_ago
FROM yearlycounts               s1
         LEFT JOIN yearlycounts s2 ON s1.from_current = s2.from_current + 1
WHERE s1.from_current = -1
ORDER BY from_current ASC;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search