CODEWITHSUNDEEP
mysql
Nikhil Krishnan
Nikhil Krishnan

Reputation: 63

I want to have Sum of individual columns of two different table and show it in a single query result where customer id matches?

Create or replace view cnPointsDetailsvw
as select  sum(cd.value), sum(cd1.points)
from customerdetails cd left join
customerdetails1 cd1 on cd.customerid = cd1.customerid;

The problem is that the above query is calculating sum multiple times for the column cd1.points

Upvotes: 2

Views: 49

Answers (2)

Hotdin Gurning
Hotdin Gurning

Reputation: 1819

If table customerdetails1 has only 1 row, so why you use SUM() function?
Just use MAX().
I am confused of your table, so let me give a sample structurs and data.

table1

id   points
-----------
1    10
2    20
3    40

table2 

id   points
-----------
1    10
1     2
1     4
2    20
3    40
3     5

And your query should be looks like this : 

CREATE OR REPLACE VIEW view_name AS 
SELECT t1.id,max(t1.points) as points1, sum(t2.points) as points2
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
GROUP BY t1.id

Your view should be looks like this : 

id  points1   points2
---------------------
1      10      16
2      20      20
3      30      45

Upvotes: 1

Pred
Pred

Reputation: 9042

Do the calculation in subqueries, then join their results:

SELECT
 CD.sum_value, CD1.sum_points
FROM
 (SELECT sum(value) as sum_value FROM customerdetails) CD
 INNER JOIN (SELECT sum(points) AS sum_points FROM customerdetails1) CD1
   ON 1 = 1

Please note, that SUM() returns NULL if there were no matching rows, so the subqueries will return with exactly one record -> any ON condition will be fine which results to true.

If you want to group by customers, then do the grouping in the subqueries:

SELECT
 CD.customerid, CD.sum_value, CD1.sum_points
FROM
 (
   SELECT customerid, sum(value) as sum_value
   FROM customerdetails
   GROUP BY customerid
 ) CD
 LEFT JOIN
 (
   SELECT customerid, sum(points) AS sum_points
   FROM customerdetails1
   GROUP BY customerid
 ) CD1
   ON CD.customerid = CD1.customerid

UPDATE

To create a view (and bypass the limitation of MySQL), you have to create 3 views: 2 for the 2 subresults, 1 to join their results:

CREATE VIEW customer_value AS 
SELECT SUM(value) as sum_value FROM customerdetails;

CREATE VIEW customer_points AS
SELECT SUM(points) as sum_points FROM customerdetails1;

CREATE VIEW cnPointsDetailsvw AS
SELECT cv.sum_value, cp.sum_points
FROM customer_value cv
INNER JOIN customer_points cp
  ON 1=1;

Upvotes: 1

Related Questions