Postgres Calculating Weighted Average

Question

I am trying to get the weighted average of items from this example table:

Item	Value
Item1	0.10
Item2	0.15

I followed this example here how to calculate it: https://www.wikihow.com/Calculate-Weighted-Average

And tried this statement:

SELECT 
(SELECT CASE
         WHEN value <= 0.1 THEN 'very good'
         WHEN value <= 0.15
              value > 0.1 THEN 'good'
         WHEN valuey <= 0.20
              AND value > 0.15 THEN 'not that good'
         WHEN value <= 0.25
              AND value > 0.20 THEN 'bad'
         ELSE 'very bad'
       END ) AS ratings,
       COUNT (*) AS numberOfItems,
       COUNT (*) /  SUM(DISTINCT ( SELECT COUNT(DISTINCT collateral_id)
FROM   triggered.table
WHERE  id = 386)) as weightingFactor,
(COUNT (*) * (COUNT (*) /  SUM(DISTINCT ( SELECT COUNT(DISTINCT collateral_id)
FROM   triggered.table
WHERE  id = 386)))) as numberXWeightingFactor
FROM   triggered.table
WHERE  id = 386
GROUP BY stars

I am trying to get the average by doing SUM(numberXWeightingFactor) but it doesn't work. Ends up giving me error: column "numberxweightingfactor" does not exist.

Answer 1

Multiple problems. Most importantly, you seem to confuse the names ratings and stars (I replaced both with rating), and window functions do not allow DISTINCT aggregation.

This should work:

SELECT t.*
     , t.number_of_items   / c.ct_dist_coll AS weighting_factor
     , t.number_of_items^2 / c.ct_dist_coll AS number_x_weighting_factor

FROM  (
   SELECT CASE
            WHEN                   value <= 0.1  THEN 'very good'
            WHEN value >  0.1  AND value <= 0.15 THEN 'good'
            WHEN value >  0.15 AND value <= 0.20 THEN 'not that good'
            WHEN value >  0.20 AND value <= 0.25 THEN 'bad'
                                                 ELSE 'very bad' END AS rating
        , count(*) AS number_of_items
   FROM   triggered.table
   WHERE  id = 386
   GROUP  BY 1
   ) t
CROSS  JOIN (
   SELECT count(DISTINCT collateral_id) AS ct_dist_coll
   FROM   triggered.table
   WHERE  id = 386
   ) c;