Sum only when they have the same date in SQL Server-CodePudding

I am writing a query in SQL Server where I have to show the sum of the records of a column that have the same date but for now it only adds all the records regardless of date.

How can I make it so that it only adds the repeated dates of the column and not all together?

SELECT
    FechaHoraReg, 
    (SELECT SUM(CantidaIngLamina) 
     FROM MovimientoMaterial Produccion 
     WHERE IdTipoMov = '1') AS FilminaI,
    (SELECT SUM(CantidaIngresa) 
     FROM MovimientoMaterial Produccion 
     WHERE IdTipoMov = '1') AS PapelI 
FROM
    MovimientoMaterial_Produccion 
GROUP BY
    FechaHoraReg

CodePudding user response：

The following query will give you a sum of the 2 columns independently

WITH CTE_FILMINAI
AS
(
SELECT
    FECHAHORAREG,
    SUM(CANTIDAINGLAMINA) AS FILMINAI
FROM MOVIMIENTOMATERIAL_PRODUCCION 
WHERE 1=1
    AND IDTIPOMOV = '1'
GROUP BY FECHAHORAREG
), CTE_PAPELI
AS
(
SELECT 
    FECHAHORAREG, 
    SUM(CANTIDAINGRESA) AS PAPELI 
FROM MOVIMIENTOMATERIAL_PRODUCCION 
WHERE 1=1
    AND IDTIPOMOV = '1'
GROUP BY FECHAHORAREG
)
SELECT
    MP.FECHAHORAREG,
    CF.FILMINAI,
    CP.PAPELI
FROM MOVIMIENTOMATERIAL_PRODUCCION MP
    LEFT JOIN CTE_FILMINAI CF ON CF.FECHAHORAREG = MP.FECHAHORAREG
    LEFT JOIN CTE_PAPELI CP ON CP.FECHAHORAREG = MP.FECHAHORAREG

This should allow you to independently find a sum of the two columns and change the where however you need it to be. Additionally, it is a left join on the main table in the event that somehow there is FECHAHORAREG in one of the CTEs. This also could be changed depending on what you are needed it for.

CodePudding user response：

Just filter out the values to ignore via a case expression. There's no reason to involve other joins or subqueries:

select FechaHoraReg,
    sum(case when IdTipoMov = '1' -- should this actually be a numeric comparison?
        then CantidaIngLamina else 0 end) as FilminaI,
    sum(case when IdTipoMov = '1' -- should this actually be a numeric comparison?
        then CantidaIngresa else 0 end) as PapelI
from MovimientoMaterial_Produccion
group by FechaHoraReg

CodePudding user response：

The subquery needs filter by id and the date, but the date is the same of the origin query.

Try it:

SELECT
    origin.FechaHoraReg, 
    (SELECT SUM(CantidaIngLamina) 
     FROM MovimientoMaterial_Produccion AS sub 
     WHERE sub.IdTipoMov = '1' 
       AND sub.FechaHoraReg = origin.FechaHoraReg) AS FilminaI,        
    (SELECT SUM(CantidaIngresa) 
     FROM MovimientoMaterial_Produccion AS subdos 
     WHERE subdos.IdTipoMov = '1'  
       AND subdos.FechaHoraReg = origin.FechaHoraReg) AS PapelI 
FROM
    MovimientoMaterial_Produccion AS origin 
GROUP BY 
    origin.FechaHoraReg