combine 3 table with sum mysql-CodePudding

I have a problem with my query. I've managed to display data from 2 tables. Next I want to add up SALE_AMT from the 3rd table, the problem is that order by cannot, the program output adds up all data not by order by

this my query:

SELECT customer.CUST_NO, customer.CUST_NM, store.STR_CD, store.STR_NM,SUM(SALE_AMT)
FROM customer, store,cust_sale_pnt GROUP BY CUST_NO

table costumer

table cust_sale_pnt

table store

my result

can you guys help me to sum data based on grouping cust_no?

CodePudding user response：

The problem is, that you don't specify a condition that's used to combine the records of the different tables. In effect, you're creating a 'cross join'.

That is, any record of table customer is combined with any record of table store and the result is combined with any record of table cust_sale_pnt.

In effect, you get each of your 3 customers combined with each of your 6 sales. Therefore the sum over sale_amt is identical for each customer.

You want an INNER JOIN to combine the records:

SELECT c.CUST_NO, c.CUST_NM, SUM(csp.SALE_AMT) 
  FROM customer c
 INNER JOIN cust_sale_pnt csp ON c.CUST_NO = csp.CUST_NO
 INNER JOIN store ON csp.STR_CD = store.STR_CD
 GROUP BY c.CUST_NO, c.CUST_NM;