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
DISTINCT Keyword applies to all the columns you
so if you need to also do aggregate functions like
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.