Summing Columns by Date in SQL

1

My apologies if this is a redundant question but I’m new to SQL - as in I’ve never worked with it/never taken a course. I was tasked with this by my new boss so I’m giving it an honest go. Anywho, here is what I’m trying to do.

I have the info I’m looking for, I’m just trying to manipulate it. Here is my query thus far:

SELECT       SalesOrderHeader.SUBDIVISION as Subdivision, SalesOrderHeader.BillToName AS Builder, WorkTicket.WTNumber, SalesOrderDetail.JT158_WTNumber, SalesOrderDetail.ItemCode,WorkTicket.DATE_SCHEDULED as ScheduledDate, WorkTicket.WTStep

FROM            SalesOrderHeader INNER JOIN
                         SalesOrderDetail ON SalesOrderHeader.SalesOrderNo = SalesOrderDetail.SalesOrderNo INNER JOIN
                         WorkTicket ON SalesOrderHeader.SalesOrderNo = WorkTicket.SalesOrderNo AND SalesOrderDetail.WTNumber = WorkTicket.WTNumber

WHERE        (SalesOrderDetail.WTParent = 'y') AND (SalesOrderDetail.ItemCode IN ('zroughin', 'ztopout', 'ztrim')) AND (WorkTicket.DATE_SCHEDULED <> '1753-01-01') AND 
                         (WorkTicket.WTStep = '000')

GROUP BY {fn YEAR(WorkTicket.DATE_SCHEDULED)}, {fn MONTH(WorkTicket.DATE_SCHEDULED)}, SalesOrderHeader.SUBDIVISION, SalesOrderHeader.BillToName, WorkTicket.WTNumber, SalesOrderDetail.WTNumber, SalesOrderDetail.ItemCode, WorkTicket.DATE_SCHEDULED, WorkTicket.WTStep

This gives me the following:

image
image808×439 12.8 KB

What I want to do is count the ItemCode column and see the total of each one separated by month. I.E. there were 2 ZROUGHINs, 6 ZTOPOUT, and 8 ZTRIM in July and 6 ZROUGHINs, 4 ZTOPOUT, and 2 ZTRIM in August.

I tried to use Count(sum(ItemCode)) in several different places in the query but got an error saying I could not use aggregate functions inside aggregate functions.

Any help or suggestions would be greatly appreciated!

2

SQL Windows functions might be usefull here.
https://drill.apache.org/docs/sql-window-functions-introduction/

Note that you cannot just aggregate the count/sum of itemCode since the other columns are not in the same group.

So you can use the windows function to count (independantly of other columns) partitioning by month of the scheduledDate. That way you will have the total count(itemcode) in every row grouped by the month.

Hope it helps or gives you some direction.