CODEWITHSUNDEEP
sql
Mirza Asghir Baig
Mirza Asghir Baig

Reputation: 41

SQL Query with Group by clause, with sum aggreagte

i m having problem with this sql query, the query is self explanatory, so looking at it will tell u what problem i m having

SELECT customer.customerid, 
       paymentdata.paidamount, 
       paymentdata.balanceamount, 
       sum(paymentreceipt.paidamount) AS Expr1 
FROM   customer 
       INNER JOIN paymentdata 
               ON customer.customerid = paymentdata.customerid 
       INNER JOIN paymentreceipt 
               ON customer.customerid = paymentreceipt.customerid 
GROUP  BY customer.customerid

Upvotes: 0

Views: 101

Answers (5)

Tim Schmelter
Tim Schmelter

Reputation: 460168

So how do you want to calculate the other columns, you are grouping only by customerid but you want to show also paymentdata.paidamount,paymentdata.balanceamount and paymentreceipt.paidamount?

Consider that the group returns multiple rows for each group, so sql-server(or any other rdbms except MySql) doesn't know which row you want to show for the ungrouped columns. If you don't want to pick out a single row you have to aggregate the column. So e.g. by using SUM, COUNT, MAX or AVG:

For example:

SELECT customer.customerid, 
       MAX(paymentdata.paidamount) AS MaxPaid, 
       AVG(paymentdata.balanceamount) AS AverageBalance, 
       SUM(paymentreceipt.paidamount) AS ReceiptPaidSum
FROM   customer 
       INNER JOIN paymentdata 
               ON customer.customerid = paymentdata.customerid 
       INNER JOIN paymentreceipt 
               ON customer.customerid = paymentreceipt.customerid 
GROUP  BY customer.customerid

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270191

If you are doing aggregation, then you probably want aggregation functions:

SELECT customer.customerid, 
       sum(paymentdata.paidamount), 
       sum(paymentdata.balanceamount), 
       sum(paymentreceipt.paidamount) AS Expr1 
FROM   customer 
       INNER JOIN paymentdata 
               ON customer.customerid = paymentdata.customerid 
       INNER JOIN paymentreceipt 
               ON customer.customerid = paymentreceipt.customerid 
GROUP  BY customer.customerid

This probably still won't give you the right numbers, because you are getting a cross join on the payments and receipts. If you want a better answer, then you should provide sample data and expected results in the question.

Upvotes: 0

vc 74
vc 74

Reputation: 38179

You need to use an aggregate function for the columns not used in the group by clause

SELECT customer.customerid, 
       sum(paymentdata.paidamount), 
       sum(paymentdata.balanceamount), 
       sum(paymentreceipt.paidamount) AS Expr1 
FROM   customer 
       INNER JOIN paymentdata 
               ON customer.customerid = paymentdata.customerid 
       INNER JOIN paymentreceipt 
               ON customer.customerid = paymentreceipt.customerid 
GROUP  BY customer.customerid

Upvotes: 1

davek
davek

Reputation: 22915

You are missing a few terms in your group by. All columns, other than aggregated ones, need to be included.

Upvotes: 2

user2393867
user2393867

Reputation: 

SELECT customer.customerid,paymentdata.customerid,paymentreceipt.customerid  
       paymentdata.paidamount, 
       paymentdata.balanceamount, 
       paymentreceipt.paidamount AS Expr1 
FROM   customer,paymentdata,paymentreceipt
       INNER JOIN paymentdata 
               ON customer.customerid = paymentdata.customerid 
       INNER JOIN paymentreceipt 
               ON customer.customerid = paymentreceipt.customerid 
GROUP  BY customer.customerid,paymentdata.customerid,paymentreceipt.customerid

Upvotes: 0

Related Questions