I have a table with ItemID, Qty, and OrderDate
I am only querying results that have an order date of between next month (Current Month + 1) and 11 months after that.
I am trying to write a Query that gets me the SUM of the QTY by ItemID, and the COUNT of months that exists for each ItemID.
I can get so far as getting a result set that looks like this:
ITEMID Qty TMonth TYear
01.0002 392.00 1 2019
01.0002 392.00 2 2019
01.0002 392.00 3 2019
01.0002 784.00 4 2019
01.0002 784.00 5 2019
01.0002 392.00 6 2019
01.0002 784.00 7 2019
01.0002 392.00 8 2019
01.0002 392.00 9 2019
I get this by grouping on ITEMID, MONTH(OrderDate), YEAR(OrderDate) and it shows the total for each month/year. There are a total of 9 months in this example. though, you would see this repeated for each item, and its associated month/year, which if an order exists, could be 1 through 11.
I need a result set that looks like:
ITEMID Qty MnthCount
01.0002 4704.00 9
A0-B1002 3300.00 2
ZX-YT12 50.00 7
I figure there has to be some Window Function or something I can use. Is there a way of doing this, or do I need to resort to Sub-Selects or some sort of Outer Apply?
Would you like this ?
---drop table test
create table test
(ITEMID varchar(20),
Qty decimal(10,2),
TMonth int,
TYear int)
insert into test values
('01.0002',392.00,1,2019),
('01.0002',392.00,2,2019),
('01.0002',392.00,3,2019),
('01.0002',784.00,4,2019),
('01.0002',784.00,5,2019),
('01.0002',392.00,6,2019),
('01.0002',784.00,7,2019),
('01.0002',392.00,8,2019),
('01.0002',392.00,9,2019),
('A0-B1002',300.00,8,2019),
('A0-B1002',3000.00,9,2019),
('ZX-YT12',5.00,1,2019),
('ZX-YT12',5.00,2,2019),
('ZX-YT12',5.00,3,2019),
('ZX-YT12',5.00,4,2019),
('ZX-YT12',5.00,5,2019),
('ZX-YT12',5.00,6,2019),
('ZX-YT12',20.00,7,2019)
select ITEMID,sum(Qty) as Qty, count(TMonth) as MnthCount
from test group by ITEMID
/*
ITEMID Qty MnthCount
-------------------- --------------------------------------- -----------
01.0002 4704.00 9
A0-B1002 3300.00 2
ZX-YT12 50.00 7
*/