0

I am trying to calculate the weighted average and have used the below query.

select di.name, count(di.name)
,dmic.c_name, 
sum(count(di.name)*dmic.c_price)/count(di.name) as avgPrice
from di
join..
on..
join..
on..
where...
group by 1,3
order by 2 desc;

And I am getting the error: aggregate function calls may not have nested aggregate or window function. How can I work around this? I am looking to get the name, its occurrences and its weighted price in the output.

rider7782
  • 1
  • 1
  • Redshift or Postgres? Those are two very different database systems –  Jan 13 '22 at 09:38
  • 1
    According to Amazon ["Amazon Redshift and PostgreSQL have a number of very important differences"](https://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html) as it was forked from PostgreSQL 8. In fact, you still need to use pgsql 8 to connect. In your case most databases would fail though. You need a subquery or a CTE to calculate the aggregates before you combine them – Panagiotis Kanavos Jan 13 '22 at 09:52
  • Does this answer your question? [Nested aggregate functions with grouping in postgresql](https://stackoverflow.com/questions/11107666/nested-aggregate-functions-with-grouping-in-postgresql) – Panagiotis Kanavos Jan 13 '22 at 09:53
  • So far I have found that Redshift is based on postgres so I added both tags. However, removing the tag to avoid confusion. – rider7782 Jan 13 '22 at 09:53
  • @PanagiotisKanavos not really. The complexity seems to be arising as I am looking for the weighted average as well as the count of the items. – rider7782 Jan 13 '22 at 10:02
  • @rider7782 not really what? You can't nest aggregates. That's what the error tells you. The solution is to produce the intermediate aggregates and combine them. That's done either with a subquery or a CTE. Have you tried either solution? – Panagiotis Kanavos Jan 13 '22 at 10:11
  • I have already tried subqueries but I need to group by count(di.name) since its an extracted field and does not give me the sum of the count as what I am expecting – rider7782 Jan 13 '22 at 10:30
  • You can use VIEWS to emulate CTEs. [unfortunately, Pg-8 did not have TEMP VIEWS yet] – wildplasser Jan 13 '22 at 12:51
  • @rider7782: Redshift was never "forked" from Postgres. Amazon only took the code of the Postgres SQL _parser_ - everything else is completely different. In my opinion Amazon's claim that Redshift is "based" on Postgres is a marketing lie. –  Jan 13 '22 at 13:21

0 Answers0