Postgres Calculating Weighted Average

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

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;