Link to home
Create AccountLog in
Avatar of rcowen00
rcowen00Flag for United States of America

asked on

MySql Query

I have 3 tables loan, audit, loanFinding.  I am trying to have a query that will give me results as follows.  The loan table gives me the name.  The audit count is pulled from audit, and Avg Issues is coming from the loanFinding table.

Name             Audit Ct      Avg Issues
Jones, Tom      3                    4.2
Smith, Susan    5                    1.2

The attached query returns

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Name SUM(case WHEN audit.auditID is NOT NULL THEN 1 ELSE 0 end)as Audits FROM lo' at line 1"

I haven't been able to successfully get an average either.  Any help is appreciated.  Thanks!

SELECT UW Name, CONCAT (uwLastName, uwFirstName) AS UW Name 
SUM(case WHEN audit.auditID is NOT NULL THEN 1 ELSE 0 end)as Audits 
FROM loan 
JOIN audit USING (loanKey) 
RIGHT JOIN loanFinding USING(auditID) 
WHERE branchKey='66' AND loan.active='1' AND audit.auditTypeKey='1' AND audit.trackingDate BETWEEN '2010-09-01' AND '2010-09-30' GROUP BY uwLastName

Open in new window

Avatar of Sharath S
Sharath S
Flag of United States of America image

Can you provide some sample data from three tables?
Can you try this
SELECT CONCAT(uwLastName,uwFirstName) AS UW_Name, 
         SUM(CASE 
               WHEN audit.auditID IS NOT NULL THEN 1 
               ELSE 0 
             END) AS Audits 
    FROM loan 
         JOIN audit 
           ON loan.loanKey = audit.loanKey 
         RIGHT JOIN loanFinding 
           ON loanFinding.auditID = loan.auditID 
   WHERE branchKey = '66' 
         AND loan.active = '1' 
         AND audit.auditTypeKey = '1' 
         AND audit.trackingDate BETWEEN '2010-09-01' AND '2010-09-30' 
GROUP BY CONCAT(uwLastName,uwFirstName)

Open in new window

Avatar of rcowen00

ASKER

Sharath,

I had to change your suggestion a tab loan.auditID should have been audit.auditID but it did work.  I didn't get any results returned though.  I am going to do a var_dump and see what I come up with.
What is your logic to find Audit count and average issues?
I think this is what you are looking for:  Audit count is straight forward, just the count of loans that were audited in a time period.  The average issues is the average number of issues noted for all loans reviewed for the period.  I want to compare one time period results to the historical time period (user entered time periods).  The final analysis will be if the persons score is increasing or decreasing between the two time periods.
Please disregard my response about the query didn't return results.  It did but the Audit count is actually counting the number of findings not the number of audits.  I don't understand why that is, shouldn't the RIGHT JOIN limit the effect of the loanFinding table on the counts?
ASKER CERTIFIED SOLUTION
Avatar of nfaria
nfaria
Flag of Portugal image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I'm getting the following error "1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COUNT(audit.auditID) as nrAudits, AVG(NrIssues) As avgIssues FROM loan l INNER' at line 3"
SELECT 
  CONCAT(uwLastName, uwFirstName) AS 'UW Name'
  COUNT(audit.auditID) as nrAudits,
  AVG(NrIssues) As avgIssues
FROM loan l
INNER JOIN audit a ON a.loanKey = l.loanKey
INNER JOIN (
SELECT auditID, COUNT(*) AS NrIssues FROM loanFinding lf GROUP BY(auditID)
) tblNrIssues ON tblNrIssues.auditID = a.auditID
WHERE 
  branchKey='66' AND 
  loan.active='1' AND 
  audit.auditTypeKey='1' AND 
  audit.trackingDate BETWEEN '2010-09-01' AND '2010-09-30' 
GROUP BY uwLastName

Open in new window

it lacks a comma after 'UW Name'

I don´t have your schema so you have to make some debug if more error appear. You should be able to make it work
The final query that worked
SELECT
    CONCAT(uwLastName, uwFirstName) AS 'UW Name',
    AVG(NrIssues) AS 'avgIssues',
    COUNT(a.auditID)as 'nrAudits'
FROM loan l
INNER JOIN audit a ON a.loanKey = l.loanKey
INNER JOIN (
SELECT auditID, COUNT(*) AS NrIssues FROM loanFinding lf GROUP BY(auditID)
) tblNrIssues ON tblNrIssues.auditID = a.auditID
WHERE
  branchKey='66' AND
  l.active='1' AND
  a.auditTypeKey='1' AND
  a.trackingDate BETWEEN '2010-09-01' AND '2010-09-30'
GROUP BY uwLastName

Open in new window