How do I use solutions from 2 calculations for another in mysql?-CodePudding

In a library data base, I am supposed to find the percentage of loans that goes overdue.

(
  LoanID int not null,
  Cop_Av int not null, -- value 1-10
  LoanDt datetime not null,
  Cop_OnLoan int not null, -- VALUE 1 or 2
  MemID int not null, -- VALUE BTWN 1-1200
  DueDt datetime not null,
  ReturnDt datetime not null
)
;

SELECT concat((OverDue/Total)*100) AS Percentage_OverDue
FROM (SELECT COUNT(*) AS Total
 FROM loanlist 
 INNER JOIN (
     SELECT COUNT(*) AS OverDue
 FROM loanlist
     WHERE ReturnDt > DueDt) )
;``` 

I have the aliases, but why is it still showing (Error Code: 1248. Every derived table must have its own alias )?

I also tried

SELECT concat((OverDue/Total)100) AS Percentage_OverDue FROM (SELECT COUNT() AS Total FROM loanlist INNER JOIN ( SELECT COUNT(*) AS OverDue FROM loanlist WHERE ReturnDt > DueDt) as a) as b;

But it gave another error; Error Code: 1054. Unknown column 'OverDue' in 'field list'

What am I missing?

CodePudding user response：

Like the error message suggests, you need to provide an aliase for a derived table. In you query , there are two derived tables missing their aliases. Furthermore, using aggregate functions on top of INNER JOIN is not the most prudent choice here. Try using a subquery to return a value in the SELECT clause.

SELECT concat((OverDue/Total)*100) AS Percentage_OverDue
FROM (SELECT COUNT(*) AS Total, 
        (SELECT COUNT(*) -- This subquery
        FROM loanlist    --  returns a value 
        WHERE ReturnDt > DueDt) AS OverDue -- in the SELECT list 
     FROM loanlist ) t -- The derived table t will be used as the source table for the main query.
;