CODEWITHSUNDEEP
mysqlgroup-byaggregate-functions
NetFlash
NetFlash

Reputation: 85

mysql reference not supported to group function

I have a MySQL procedure that takes in 2 parameters par_DateFrom and par_DateTo

I'm getting a nasty error. I'm pretty sure that reusing alias TotalDaysOut to calculate TotalIncome is the culprit. How can I fix this elegantly?

Error 1247: reference TotalDaysOutnot supported reference to group function

BEGIN
SELECT  t.LicencePlate
            ,f.Make
            ,f.Model
            ,f.Year

            ,COUNT(t.LicencePlate) AS TotalTrx          
            ,SUM(DATEDIFF(IF(checkedIn >par_DateTo, par_DateTo, checkedIn) ,IF(checkedOut <par_DateFrom, par_DateFrom, checkedOut))) AS TotalDaysOut
            ,SUM(t.Price* (SELECT TotalDaysOut)) AS TotalIncome
FROM        TRANSACTIONS    t
INNER JOIN FLEET            f
    ON t.LicencePlate = f.LicencePlate
WHERE t.CheckedOut < par_DateTo AND t.CheckedIn > par_DateFrom
GROUP BY t.LicencePlate
            ,f.Make
            ,f.Model
            ,f.Year;
END

Upvotes: 0

Views: 4624

Answers (2)

spencer7593
spencer7593

Reputation: 108370

Since your predicates are already verifying that all of the columns referenced in the expression are not null:

checkedIn
par_DateTo
checkedOut
par_DateFrom

(The predicates in the WHERE clause require all of those to be non-NULL), you could simplify the expression a bit, to reference each column once, rather than twice:

DATEDIFF(LEAST(t.checkedIn, par_DateTo),GREATEST(t.checkedOut, par_DateFrom))

And (as Gordon already suggested) just repeat that expression where the result is needed.

When we absolutely, positively have to have reference to an alias from a query, the only real option in MySQL is to use an inline view, though this approach has significant performance consequences for large sets.

SELECT v.LicencePlate
     , f.Make
     , f.Model
     , f.Year
     , COUNT(v.LicencePlate)  AS TotalTrx
     , SUM(v.DaysOut)         AS TotalDaysOut
     , SUM(v.DaysOut)*v.Price AS TotalIncome
  FROM ( SELECT t.LicencePlate
              , t.Price
              , DATEDIFF(
                  LEAST(t.checkedIn, par_DateTo),
                  GREATEST(t.checkedOut, par_DateFrom)
                ) AS DaysOut
          FROM TRANSACTIONS t
         WHERE t.CheckedOut < par_DateTo
           AND t.CheckedIn  > par_DateFrom
       ) v
  JOIN FLEET f
    ON f.LicencePlate = v.LicencePlate
 GROUP
    BY v.LicencePlate
     , f.Make
     , f.Model
     , f.Year

That's less performant, and less elegant, than just simplifying and repeating the expression.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can't do that. You cannot reference a column alias at the same level of the select as where it is defined. I'm not sure what the exact error is but the (select TotalDaysOut) doesn't make sense.

So, repeat the expression with the additional multiplication:

     SUM(DATEDIFF(IF(checkedIn >par_DateTo, par_DateTo, checkedIn) ,IF(checkedOut <par_DateFrom, par_DateFrom, checkedOut))) AS TotalDaysOut,
     SUM(t.Price * DATEDIFF(IF(checkedIn >par_DateTo, par_DateTo, checkedIn) ,IF(checkedOut <par_DateFrom, par_DateFrom, checkedOut))) AS TotalIncome

Upvotes: 1

Related Questions