Search code examples
sqlwindev

How to fix " Aggregate functions (COUNT, AVG, SUM, MIN, MAX) are not allowed outside SELECT or HAVING clauses" Error in HFSQL

I am using control center HFSQL of windev 20 to execute a query. I would like to calculate the time and date difference, between the maximum and the minimum MAX (hour) and MIN (hour) for each service and also the number of days if there is.

I tried the functions DateDifference and DateTimeDifference but unfortunately I have an error.

the following code is just to see the maximum and the minimum hour for each service it works perfectly but not what i want :

SELECT  Service.Libellé AS Libellé,  
Min(DetailCircuitFacture.Heure),MAX(DetailCircuitFacture.Heure) 
FROM detailcircuitfacture
joIN Service on
Service.CodeSce=detailcircuitfacture.CodeSce
group by Service.Libellé

i want to make difference of date and hour between MAX and Min for each service like so :

SELECT Service.Libellé AS Libellé, WL.DateDifférence(Min(DetailCircuitFacture.DATE),
Max(DetailCircuitFacture.DATE)) AS Nombre_jours,
    WL.DateHeureDifférence(Min(DetailCircuitFacture.Heure),Max(DetailCircuitFacture.Heure)) AS Nombre_heurs
    FROM detailcircuitfacture
    JOIN Service on
    Service.CodeSce=detailcircuitfacture.CodeSce
    group by Service.Libellé

I expect the output without error, but the actual output is

Error in the SQL code of the <> request. Initialization of the query impossible. Aggregate functions (COUNT, AVG, SUM, MIN, MAX) are not allowed outside SELECT or HAVING clauses

expect result

expected result

Thanks by advance

Solution

  • I would say your issue is trying to use the result of an aggregate function as a function parameter.

    You could solve this problem with a subquery:

      SELECT Libellé,
         WL.DateDifférence(min_date, max_date) AS Nombre_jours,
         WL.DateHeureDifférence(min_heurs, max_heurs) AS Nombre_heurs
    FROM (
     SELECT Service.Libellé AS Libellé, 
            MIN(DetailCircuitFacture.DATE) AS min_date,
            MAX(DetailCircuitFacture.DATE) AS max_date,
            MIN(DetailCircuitFacture.Heure) AS min_heurs,
            MAX(DetailCircuitFacture.Heure) AS max_heurs
       FROM detailcircuitfacture
       JOIN Service ON Service.CodeSce=detailcircuitfacture.CodeSce
   GROUP BY Service.Libellé
     ) core
 GROUP BY Libellé;

    EDIT: since the original question was updated to include that it is about HFSQL, I should say I have no experience using HFSQL. This answer is based on basic SQL syntax shared by more common databases like MySQL, PostgreSQL, and SQL Server.