postgresql nesting of sum and count functions

Question

I am trying to use nested aggregate functions. My Query

SELECT 
  sale_order.client_order_ref AS CHANNEL, 
  product_category.name AS BRAND,
  COUNT(sale_order_line.name) AS QTY_SOLD,
  SUM(sale_order.amount_total) AS SELLING_COST,
  SUM(COUNT(sale_order_line.name) * 4.5) AS PACK_COST,
  SUM(COUNT(sale_order_line.name) * 30.5) AS EKO_COMM
FROM 
  public.sale_order, 
  public.sale_order_line, 
  public.product_template, 
  public.product_product, 
  public.product_category
WHERE 
  sale_order_line.order_id = sale_order.id AND
  sale_order_line.product_id = product_product.id AND
  product_template.categ_id = product_category.id AND
  product_product.product_tmpl_id = product_template.id AND
  product_product.default_code = sku_cost.product_code AND
  TO_CHAR(sale_order.date_order, 'MON YYYY') = 'JUN 2015'
GROUP BY
  CHANNEL,
  BRAND
ORDER BY
  CHANNEL,
  BRAND

How to nest sum and count? I tried to use a subquery but didn't work.

Giorgi Nakeuri · Accepted Answer · 2015-06-24 09:37:27Z

You do nested queries not the aggregations:

SELECT  CHANNEL ,
        BRAND ,
        QTY_SOLD ,
        SELLING_COST ,
        SUM(PACK_COST) AS PACK_COST ,
        SUM(EKO_COMM) AS EKO_COMM
FROM    ( SELECT    sale_order.client_order_ref AS CHANNEL ,
                    product_category.name AS BRAND ,
                    COUNT(sale_order_line.name) AS QTY_SOLD ,
                    SUM(sale_order.amount_total) AS SELLING_COST ,
                    COUNT(sale_order_line.name) * 4.5 AS PACK_COST ,
                    COUNT(sale_order_line.name) * 30.5 AS EKO_COMM
          FROM      public.sale_order ,
                    public.sale_order_line ,
                    public.product_template ,
                    public.product_product ,
                    public.product_category
          WHERE     sale_order_line.order_id = sale_order.id
                    AND sale_order_line.product_id = product_product.id
                    AND product_template.categ_id = product_category.id
                    AND product_product.product_tmpl_id = product_template.id
                    AND product_product.default_code = sku_cost.product_code
                    AND TO_CHAR(sale_order.date_order, 'MON YYYY') = 'JUN 2015'
          GROUP BY  CHANNEL ,
                    BRAND
        ) t
GROUP BY CHANNEL ,
        BRAND ,
        QTY_SOLD ,
        SELLING_COST

Collectives™ on Stack Overflow

postgresql nesting of sum and count functions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related