Rookie Recruits Rookie Recruits - 4 months ago 7
SQL Question

sql join query not returning data, just blank

I am trying to pull data from 2 separate tables but only specific columns, then I do a join so that only one row per client is displaying with the total sum of their payments but the information is not displaying. Code is below. It is for a reports page so think about geting the sum of all payments. I know what I am looking for, I just think that maybe there is a bug in the query that I can't seem to catch. I could use an extra pair of eyes to point out the flaw if possible. Thanks

SELECT pre.id, pre.loanAmount, pre.custId,
SUM(pay.amount) AS amount,
DISTINCT(pay.company) AS company,
DISTINCT(pay.loanId) AS loanId
FROM preQualForm pre INNER JOIN
payments pay
ON pre.custId=pay.custId

Answer

The DISTINCT Keyword applies to all the columns you SELECT, so if you need to also do aggregate functions like SUM, then it is better achieved using a GROUP BY clause on non-aggregate columns.

The following should work.

SELECT   
    pre.id
    , pre.loanAmount
    , pre.custId
    , SUM(pay.amount) AS Amount
    , pay.company AS Company
    , pay.loanId AS LoanId
FROM preQualForm pre 
     INNER JOIN payments pay
     ON pre.custId = pay.custId
GROUP BY
    pre.id
    , pre.loanAmount
    , pre.custId
    , pay.company
    , pay.loanId

See Also: GROUP BY - Simple GROUP BY Example.

Comments