"correct" view

--
-- TOC entry 2690 (class 1259 OID 17197646)
-- Name: sku_average_costs_in_our_possession; Type: VIEW; Schema: admin_views; 
--

CREATE VIEW sku_average_costs_in_our_possession AS
 SELECT skus.id AS sku_id,
    skus.id,
    skus.product_id,
    skus.created_at,
    skus.updated_at,
    skus.size,
    skus.location,
    skus.upc_code,
    skus.deactivated_at,
    skus.scn,
    skus.origin,
    skus.product_look_id,
    skus.msrp_price,
    skus.sale_price,
    skus.vendor_bar_code,
    skus.shipping_surcharge,
    skus.force_ground_shipping,
    skus.is_webstore_sku,
    skus.webstore_sku_id,
    skus.foreign_msrp_price,
    skus.foreign_currency,
    skus.weight,
    skus.length,
    skus.width,
    skus.height,
    skus.is_exit_inventory,
    skus.is_giveaway,
    skus.extra_weight,
    skus.vendor_id,
    skus.is_sellable,
    skus.primary_size_chart_column_id,
    skus.size_id,
    skus.ship_alone_qty,
    skus.sku_attribute_set_id,
    skus.ship_method_id,
    skus.vendor_style_number,
    skus.vendor_color,
    skus.data_source_key,
    skus.country_of_origin_id,
    (sum(units.base_price))::numeric(10,2) AS total_cost,
    (avg(units.base_price))::numeric(10,2) AS total_average_cost,
    count(DISTINCT units.id) AS total_units,
    (sum(units.base_price) FILTER (WHERE (NOT units.is_virtual)))::numeric(10,2) AS physical_cost,
    (avg(units.base_price) FILTER (WHERE (NOT units.is_virtual)))::numeric(10,2) AS physical_average_cost,
    count(DISTINCT units.id) FILTER (WHERE (NOT units.is_virtual)) AS physical_units,
    (sum(units.base_price) FILTER (WHERE units.is_virtual))::numeric(10,2) AS virtual_cost,
    (avg(units.base_price) FILTER (WHERE units.is_virtual))::numeric(10,2) AS virtual_average_cost,
    count(DISTINCT units.id) FILTER (WHERE units.is_virtual) AS virtual_units, 
    (sum(units.base_price) FILTER (WHERE units.is_consignment))::numeric(10,2) AS consignment_cost,
    (avg(units.base_price) FILTER (WHERE units.is_consignment))::numeric(10,2) AS consignment_average_cost,
    count(DISTINCT units.id) FILTER (WHERE units.is_consignment) AS consignment_units
   FROM (public.skus
     LEFT JOIN public.units ON (((units.webstore_sku_id = skus.id) AND (units.unit_disposition_id = 1))))
  GROUP BY skus.id;