Grouping Sum of Items by Month

Question

I have a view for all invoiced transactions with fields item, qty_sold, and invoice_date. It displays like this.

Item	QTY_Shipped	Invoice_Date
Apple	2	02/01/2021
Apple	4	10/25/2021
Pear	15	05/10/2021
Strawberry	10	04/10/2021
Nectarine	405	08/01/2021
Strawberry	5	10/01/2021
Apple	5	02/25/2021

And, I am trying to sum all items according to the month/period like the table below.

Year	Period	Item	Sum_QTY_Shipped
2021	2	Apple	7
2021	4	Strawberry	10
2021	5	Pear	15
2021	8	Nectarine	405
2021	10	Strawberry	5
2021	10	Apple	4

I am using the below query and have tried using the SUM function but keep getting a 'Column 'v_sales_by_order_date.Invoice_Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.' message.

    select YEAR(Invoice_Date) as 'Year', MONTH(Invoice_Date) as 'Period', Item, Qty
    from v_sales_by_order_date
    order by Invoice_Date

I don't see any `sum` function in your query? – Stu Feb 28 '22 at 20:38 — Stu, Feb 28 '22 at 20:38

score 0 · Answer 1 · answered Feb 28 '22 at 20:43

You need to use SUM() and GROUP BY as follows

SELECT
  YEAR(Invoice_Date) as 'Year',
  MONTH(Invoice_Date) as 'Period',
  Item,
  SUM(QTY) total
FROM
  v_sales_by_order_date
GROUP BY
  YEAR(Invoice_Date),
  MONTH(Invoice_Date),
  Item
ORDER BY
  YEAR(Invoice_Date),
  MONTH(Invoice_Date),
  Item;

Grouping Sum of Items by Month

1 Answers1