Mysql Sum distinct row

Question

I want to sum the value of 3 columns from 3 tables

My query:

SELECT sum(s.paid_money+d.paid_money+e.paid_money)
       FROM payment_dates AS dates
       LEFT JOIN supplier_payments AS s 
                ON dates.id = s.payment_date_id
       LEFT JOIN daily_payments AS d 
                ON dates.id = d.payment_date_id
       LEFT JOIN emp_payments AS e 
                ON dates.id = e.payment_date_id;

When i select paid_money from 3 tables by the following query:

 SELECT dates.id, s.paid_money as sup, d.paid_money as daily, e.paid_money as emp  
       FROM payment_dates AS dates  
       LEFT JOIN supplier_payments AS s 
                ON dates.id = s.payment_date_id
       LEFT JOIN daily_payments AS d 
                ON dates.id = d.payment_date_id
       LEFT JOIN emp_payments AS e 
                ON dates.id = e.payment_date_id;

-The output
id sup daily emp
'1', NULL, NULL, NULL
'2', '75', '37', NULL
'2', '75', '255', NULL
'2', '669', '37', NULL
'2', '669', '255', NULL
'3', '575', '65', NULL
'3', '575', '909', NULL
'3', '575', '160', NULL
'3', '575', '60', NULL
'3', '575', '100', NULL
'3', '575', '624.5', NULL
'3', '575', '70', NULL
'3', '423', '65', NULL
'3', '423', '909', NULL
'3', '423', '160', NULL
'3', '423', '60', NULL
'3', '423', '100', NULL
'3', '423', '624.5', NULL
'3', '423', '70', NULL

• For example Id=2 duplicate 4 time
• And sup with value of 75 duplicate twice time and 669 duplicate twice .
• With id=3 sup of 575 duplicate 7 time .
• I want to sum only one of each values
• Like sum(75+669+575+423)
  And the final output
• Like SUM(SUM(sup)+SUM(daily)+SUM(emp)) WHERE dates.pay_date = 'some date'

Answer 1

Do you mean something like this?

SELECT dates.id, 
((SELECT SUM(COALESCE(s.paid_money,0)) FROM supplier_payments WHERE s.payment_date_id = dates.id )+
(SELECT SUM(COALESCE(d.paid_money,0)) FROM daily_payments WHERE d.payment_date_id = dates.id ) +
(SELECT SUM(COALESCE(e.paid_money,0)) FROM emp_payments WHERE e.payment_date_id = dates.id )) as total
FROM payment_dates AS dates

If this is not what you meant, please provide a brief example of the data in the source tables (i.e. supplier_payments daily_payments and emp_payments) and what the expected result should be.

UPDATE

I've built a sample DB with table structures like this:

CREATE TABLE `daily_payments` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `paid_money` decimal(12,2) NOT NULL,
 `payment_date_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
);

CREATE TABLE `emp_payments` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `paid_money` decimal(12,2) DEFAULT NULL,
  `payment_date_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `payment_dates` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `pay_date` date NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `supplier_payments` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `payment_date_id` int(11) DEFAULT NULL,
  `paid_money` decimal(12,2) NOT NULL,
  PRIMARY KEY (`id`)
);

After inserting some random data, this query seems to return your desired output:

SELECT id,
COALESCE((SELECT SUM(`daily_payments`.`paid_money`) FROM `daily_payments` WHERE    `payment_date_id`=`payment_dates`.id),0) + 
COALESCE((SELECT SUM(`emp_payments`.`paid_money`) FROM `emp_payments` WHERE `payment_date_id`=`payment_dates`.id),0) + 
COALESCE((SELECT SUM(`supplier_payments`.`paid_money`) FROM `supplier_payments` WHERE `payment_date_id`=`payment_dates`.id),0) as sum_of_payments
FROM payment_dates WHERE pay_date = '2012-09-23'

The COALESCE function is to avoid NULL values in case no record is found in one or more of the tables.

Answer 2

You mean this?

SELECT sum(s.paid_money+d.paid_money+e.paid_money)
       FROM payment_dates AS dates
       LEFT JOIN supplier_payments AS s 
                ON dates.id = s.payment_date_id
       LEFT JOIN daily_payments AS d 
                ON dates.id = d.payment_date_id
       LEFT JOIN emp_payments AS e 
                ON dates.id = e.payment_date_id;
       GROUP BY dates.id