Postgresql - Aggregate queries inside aggregate queries

Question

I'm working on building a select statement for a sales rep commission report that uses postgresql tables. I want it to show these columns: -Customer No.

-Part No.

-Month-to-date Qty (MTD Qty)

-Year-to-date Qty (YTD Qty)

-Month-to-date Extended Selling Price (MTD Extended)

-Year-to-date Extended Selling Price (YTD Extended)

The data is in two tables:

Sales_History (one record per invoice and this table includes Cust. No. and Invoice Date)

Sales_History_Items (one record per part no. per invoice and this table includes Part No., Qty and Unit Price).

If I do a simple query that combines these two tables, this is what it looks like:

Date / Cust / Part / Qty / Unit Price

Apr 1 / ABC Co. / WIDGET / 5 / $11

Apr 4 / ABC Co. / WIDGET / 8 / $11.50

Apr 1 / ABC Co. / GADGET / 1 / $30

Apr 7 / XYZ Co. / WIDGET / 3 / $11.50

etc.

This is the final result I want (one line per customer per part):

Cust / Part / Qty / MTD Qty / MTD Sales / YTD Qty / YTD Sales

ABC Co. / WIDGET / 13 / $147 / 1500 / $16,975

ABC Co. / GADGET / 1 / $30 / 7 / $210

XYZ Co. / WIDGET / 3 / $34.50 / 18 / $203.40

I’ve been able to come up with this SQL statement so far, which does not get me the extended selling columns (committed_qty * unit_price) per line and then summarize them by cust no./part no., and that’s my problem:

with mtd as
(SELECT sales_history.cust_no, part_no, Sum(sales_history_items.committed_qty) AS MTDQty
    FROM sales_history left JOIN sales_history_items 
    ON sales_history.invoice_no = sales_history_items.invoice_no where  sales_history_items.part_no is not null and sales_history.invoice_date >= '2020-04-01' and sales_history.invoice_date <= '2020-04-30' 
    GROUP BY sales_history.cust_no, sales_history_items.part_no),

ytd as 
(SELECT sales_history.cust_no, part_no, Sum(sales_history_items.committed_qty) AS YTDQty
    FROM sales_history left JOIN sales_history_items 
    ON sales_history.invoice_no = sales_history_items.invoice_no where sales_history_items.part_no is not null and sales_history.invoice_date >= '2020-01-01' and sales_history.invoice_date <= '2020-12-31' GROUP BY sales_history.cust_no, sales_history_items.part_no),

mysummary as
(select MTDQty, YTDQty, coalesce(ytd.cust_no,mtd.cust_no) as cust_no,coalesce(ytd.part_no,mtd.part_no) as part_no
from ytd full outer join mtd on ytd.cust_no=mtd.cust_no and ytd.part_no=mtd.part_no)

select * from mysummary;

I believe that I have to nest another couple of aggregate queries in here that would group by cust_no, part_no, unit_price but then have those extended price totals (qty * unit_price) sum up by cust_no, part_no.

Any assistance would be greatly appreciated. Thanks!

Answer 1

If I follow you correctly, you can do conditional aggregation:

select sh.cust_no, shi.part_no,
    sum(shi.qty)  mtd_qty, 
    sum(shi.qty * shi.unit_price) ytd_sales,
    sum(shi.qty) filter(where sh.invoice_date >= date_trunc('month', current_date) mtd_qty, 
    sum(shi.qty * shi.unit_price) filter(where sh.invoice_date >= date_trunc('month', current_date) mtd_sales 
from sales_history sh
left join sales_history_items shi on sh.invoice_no = shi.invoice_no 
where  shi.part_no is not null and sh.invoice_date >= date_trunc('year', current_date)
group by sh.cust_no, shi.part_no

The logic is to filter on the current year, and use simple aggregation to compute the "year to date" figures. To get the "month to date" columns, we can just filter the aggregate functions.

Answer 2

Do this in one go with filter expressions:

with params as (
  select '2020-01-01'::date as year, 4 as month
)
SELECT h.cust_no, i.part_no, 
       SUM(i.committed_qty) AS YTDQty,
       SUM(i.committed_qty * i.unit_price) as YTDSales,
       SUM(i.committed_qty) FILTER 
         (WHERE extract('month' from h.invoice_date) = p.month) as MTDQty,
       SUM(i.committed_qty * i.unit_price) FILTER
         (WHERE extract('month' from h.invoice_date) = p.month) as MTDSales
  FROM params p
       CROSS JOIN sales_history h
       LEFT JOIN sales_history_items i
              ON i.invoice_no = h.invoice_no 
 WHERE i.part_no is not null 
   AND h.invoice_date >= p.year 
   AND h.invoice_date < p.year + interval '1 year' 
 GROUP BY h.cust_no, i.part_no