SUBQUERIES with a condition SQL Server-CodePudding

I would like to ask for a little help in SQL Server.

I need to sum $ the things that the client bought.

My subquery only sums the total of the entire table, but I need something like

Numero D               TotalItem    TotalBOUGHT
-----------------------------------------------
111                     800           1200
111                     200           1200
111                     100           1200
111                     100           1200
455                     200            300
455                     100            300

This is my code

SELECT
    NumeroD, Descrip AS ClientName, CodClie AS ID, 
    Descrip1 AS Description,  TotalItem, CodUbic, 
    Día_Transaccion AS Day, Mes AS Month, ANO AS YEAR, 
    (SELECT SUM(TotalItem) FROM FORMULAFINAL) AS TotalAmount
FROM 
    FORMULAFINAL 
WHERE 
    Descrip LIKE 'Hector%' 
GROUP BY 
    TotalItem, NumeroD, Descrip, CodClie, Descrip1, 
    TotalItem, CodUbic, Día_Transaccion, Mes, Ano
ORDER BY 
    Ano DESC, NumeroD, Descrip, Descrip1, CodClie, TotalItem, 
    CodUbic, Día_Transaccion, Mes

enter image description here

CodePudding user response：

Maybe use windowing functions like below

select  
NumeroD, Descrip AS ClientName, CodClie AS ID, Descrip1 AS Description,  TotalItem, CodUbic, Día_Transaccion AS Day, Mes AS Month, ANO AS YEAR, 
    SUM(TotalItem) OVER (partition by NumeroD, DAY, MONTH, YEAR ) AS TotalAmount
    FROM FORMULAFINAL 
    WHERE Descrip LIKE 'Hector%' 
ORDER BY Ano DESC, NumeroD, Descrip, Descrip1, CodClie, TotalItem, CodUbic, Día_Transaccion, Mes