Sql-server – Sql Group by on subquery rows

group byMySQLsql serversubquery

I have this table :
ArretProductionJournee(Id, DateArret,HeureDebut,HeureFin,EnumArret)
Example :

DateArret   ||HeureDebut                 ||HeureFin                 ||EnumArret
2020-11-30  ||2020-11-30 14:00:00.000    ||2020-11-30 15:00:00.000  ||PS
2020-11-30  ||2020-11-30 16:00:00.000    ||2020-11-30 17:00:00.000  ||HI

i want to sum the datediff(HeureDebut,HeureFin) in columns for each EnumArret
so i run this query :

SELECT ArretProductionJournee.DateArret, 
(select
sum (datediff(minute,ArretProductionJournee.HeureDebut, 
ArretProductionJournee.HeureFin))
where ArretProductionJournee.EnumArret Like 'HI')as HI,

(select
sum (datediff(minute,ArretProductionJournee.HeureDebut, 
ArretProductionJournee.HeureFin))
where ArretProductionJournee.EnumArret Like 'PS') as PS

FROM            dbo.ArretProductionJournee
                where ArretProductionJournee.EnumArret Like 'HI'OR 
                ArretProductionJournee.EnumArret Like 'PS'
group by  ArretProductionJournee.EnumArret, dbo.ArretProductionJournee.DateArret

Result :

DateArret   ||HI   ||   PS
2020-10-30  ||12   ||   NULL
2020-11-30  ||60   ||   NULL
2020-11-30  ||NULL ||   60

The result i want is Grouping the sum by the date and the enumArret:

DateArret   ||HI   ||   PS
2020-10-30  ||12   ||   0
2020-11-30  ||60   ||   60

Best Answer

Rather than subqueries, use conditional aggregation:

SELECT
      DateArret
    , SUM(CASE EnumArret WHEN 'HI' THEN DATEDIFF(minute,HeureDebut, HeureFin) ELSE 0 END) AS HI
    , SUM(CASE EnumArret WHEN 'PS' THEN DATEDIFF(minute,HeureDebut, HeureFin) ELSE 0 END) AS PS
FROM dbo.ArretProductionJournee
WHERE EnumArret IN('HI', 'PS')
GROUP BY
    DateArret
ORDER BY
    DateArret;

Below is more complete sample data and schema to match your desired result.

CREATE TABLE dbo.ArretProductionJournee(
     ID int NOT NULL IDENTITY CONSTRAINT PK_ArretProductionJournee PRIMARY KEY
    ,DateArret date NOT NULL
    ,HeureDebut datetime2(3) NOT NULL
    ,HeureFin datetime2(3) NOT NULL
    ,EnumArret char(2) NOT NULL
);
INSERT INTO dbo.ArretProductionJournee(DateArret, HeureDebut, HeureFin, EnumArret)
VALUES
     ('2020-10-30', '2020-10-30 15:00:00.000', '2020-10-30 15:05:00.000', 'HI')
    ,('2020-10-30', '2020-10-30 15:00:00.000', '2020-10-30 15:07:00.000', 'HI')
    ,('2020-11-30', '2020-11-30 14:00:00.000', '2020-11-30 15:00:00.000', 'PS')
    ,('2020-11-30', '2020-11-30 16:00:00.000', '2020-11-30 17:00:00.000', 'HI')
    ,('2020-11-30', '2020-11-30 16:00:00.000', '2020-11-30 17:00:00.000', 'XX');