Search code examples
sqlsql-servernullsubquery

Sql remove Null Values in sub queries

I am trying to do sub select queries but i am having Null values in my group by

SELECT        convert(varchar, dbo.ArretProductionJournee.DateArret, 3) , 
(select
sum (datediff(minute, ArretProductionJournee.HeureDebut, ArretProductionJournee.HeureFin)  ) 
where ArretProductionJournee.EnumArret Like 'HH')
as HH, 
(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 dbo.ArretProductionJournee.DateArret BETWEEN '01/04/2021'and '03/04/2021'
group by ArretProductionJournee.EnumArret, convert(varchar, dbo.ArretProductionJournee.DateArret, 3)

This results like below :

enter image description here

I want to remove those Null Values to have a result like so :

---------------------------
Date Arrêt | HH | HI | PS |
---------------------------
03 / 02/ 21| 0  | 29 | 45 |
Solution

  • I guess you do not need those sub-SELECTs. Use conditional aggregation instead. Try this.

    SELECT CONVERT(varchar, DateArret, 3), 
       SUM(IIF(EnumArret Like 'HH', datediff(minute, HeureDebut, HeureFin, 0)) AS HH,
       SUM(IIF(EnumArret Like 'HI', datediff(minute, HeureDebut, HeureFin, 0)) AS HI,
       SUM(IIF(EnumArret Like 'PS', datediff(minute, HeureDebut, HeureFin, 0)) AS PS,
  FROM dbo.ArretProductionJournee 
 WHERE DateArret BETWEEN '01/04/2021'and '03/04/2021'
 GROUP BY CONVERT(varchar, DateArret, 3)

    It's called conditional aggregation because each SUM(IIF(condition, val, 0)) item only adds up rows matching the condition.

    I removed the table names from your column names (ArretProductionJournee.HeureDebut becomes HeureDebut) because you only use one table, and because the query is easier to read that way.