CODEWITHSUNDEEP
phpmysqlsql
user2985621
user2985621

Reputation: 65

How to Join mysql Tables With Sum

How can i join the following SQL query?

SELECT SUM( payments.amount ) , SUM( payments.balance ) , records.recordID, records.record
FROM fees AS payments
INNER JOIN recordtype AS records ON payments.referenceID = records.recordID

the above query is giving the following error

MySQL said: Documentation

1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

My Expectation results would like each recordID to show balance and amount it has.

Thank and Regards, all Suggestions & Questions are welcome

Upvotes: 1

Views: 85

Answers (2)

Shimu
Shimu

Reputation: 1147

Your SQL statemant won't work because you have not made it clear what to count. If you use sum() or max() or min() or something like that, you have to use a group by in your statement. So, e.g. if you want to calculate the sum of the amount your customers purchase in your shop use something like this:

SELECT SUM( payments.amount )
FROM payments
inner join customer on payments.customer_id = customer.id 
GROUP BY customer.id

And, if you wan't to check only one customer you have to use the SQL clouse "haveing". Where won't work in that case.

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

SELECT SUM( payments.amount ) , SUM( payments.balance ) , records.recordID, records.record
FROM fees AS payments INNER JOIN recordtype AS records 
ON payments.referenceID = records.recordID
GROUP BY records.recordID, records.record

When you have used an aggregate function in your SELECT statement you have to tell sql how to do the aggregates on that column by using GROUP BY clause,

Simple rule of thumb, When you have used an aggregate function (COUNT,MAX, MIN, SUM, AVG) in a SELECT statement any other columns that are in that SELECT and are not contained in any aggregate function MUST come in GROUP BY clause of that statement.

Upvotes: 1

Related Questions