I’m working on a report that we need to find all the products we sold by territory for a specific period. I got my report to work but as soon as I add my date column so I can filter on this my report is not giving my expected result as it wants to date column in the group by.

My Data looks like this

Item | Description | Territory | Qty     | Date
-----------------------------------------------------
A    | Desc for A  | CAN       | 10      | 2024-01-01
A    | Desc for A  | CAN       | 30      | 2024-02-01
B    | Desc for B  | USA       | 20      | 2024-01-01
C    | Desc for C  | USA       | 15      | 2024-01-01

Here’s my query for the data

SELECT 
    Item,
    description,
    Territory,
    SUM(Qty) AS QtySold,
    Date

FROM table
    
    GROUP BY Item, Territory, description, date

I’m trying to get this result, if I remove the date column from my query I get what I want

Item | Description | Territory | QtySold 
-----------------------------------------
A    | Desc for A  | CAN       | 40            
B    | Desc for B  | USA       | 20      
C    | Desc for C  | USA       | 15      

With the date column my result is still looking like the original data with the date column. How can I group by without using the date column and still be able to filter by date. GROUP BY is causing me some headaches figuring out to properly use it in MS SQL