Multiple Sum() values with GROUP BY Problem

[sanjhan]

Hi,
I am a newbie to sql,I have a problem with querying the intended result.What i need is to get the SUM() of daily income,cost im my result set, but it gives me unintended result set hence it gets duplicated by GROUP BY clause.please correct my sql.
Thank you.

this is what i want

+------------+--------+------+
| date | Income | Cost |
+------------+--------+------+
| 2010-05-12 | NULL | 1000 |
| 2010-06-10 | 1500 | 700 |
| 2010-07-07 | NULL | 1200 |
+------------+--------+------+

this is my query
----------------

SELECT DISTINCT U.date,SUM(I.amount)AS 'Income' ,SUM(A.amount) AS 'Cost' 
 
FROM  
(SELECT DISTINCT date from `income`GROUP BY date
UNION 
SELECT DISTINCT date from `advertising`GROUP BY date
) U
 
LEFT JOIN `income` I
ON U.date = I.date
LEFT JOIN `advertising` A
ON U.date = A.date
 
 GROUP BY U.date,I.amount,A.amount
 

This is what it gives me
+------------+--------+------+
| date | Income | Cost |
+------------+--------+------+
| 2010-05-12 | NULL | 1000 |
| 2010-06-10 | 500 | 300 |
| 2010-06-10 | 500 | 400 |
| 2010-06-10 | 1000 | 300 |
| 2010-06-10 | 1000 | 400 |
| 2010-07-07 | NULL | 1200 |
+------------+--------+------+
CREATE/INSERT SCRIPT
--------------------------------------------

CREATE TABLE IF NOT EXISTS `advertising` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `newspaper` varchar(50) NOT NULL,
  `details` text NOT NULL,
  `issues_day` varchar(50) NOT NULL,
  `amount` double NOT NULL,
  `created_date` varchar(50) NOT NULL,
  `created_by` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
 
 
 
INSERT INTO `advertising` (`id`, `date`, `newspaper`, `details`, `issues_day`, `amount`, `created_date`, `created_by`) VALUES
(1, '2010-07-07', 'Silumina', 'IELTS\r\n5 day express course\r\n12500/ only', '2010/04/08', 1200, '2010-07-07', 'sa'),
(5, '2010-06-10', 'Silumina', 'test test', '2010/04/05', 300, '2010-07-13', 'sa'),
(3, '2010-06-10', 'sunday observer', 'ISRA\r\nUK,USA, Canada, Cyprus', '2010/04/05', 400, '2010-07-13', 'sa'),
(4, '2010-05-12', 'sunday observer', 'test test', '2010/04/05', 1000, '2010-07-12', 'sa');
------------------------
 
 
CREATE TABLE IF NOT EXISTS `income` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `name` varchar(50) NOT NULL,
  `city_country` varchar(50) NOT NULL,
  `issues_day` varchar(50) NOT NULL,
  `birth_day` varchar(50) NOT NULL,
  `payment_date` varchar(50) NOT NULL,
  `bank` varchar(50) NOT NULL,
  `amount` double NOT NULL,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;
 
 
INSERT INTO `income` (`id`, `date`, `name`, `city_country`, `issues_day`, `birth_day`, `payment_date`, `bank`, `amount`, `created_date`, `created_by`) VALUES
(14, '2010-06-10', 'Anne', 'colombo', '2010/06/10', '1999/06/10', '2010/06/10', 'BOC', 500, '2010-07-12 13:28:53', 'sa'),
(13, '2010-06-10', 'Susantha', 'colombo', '2010/06/10', '1999/06/10', '2010/06/10', 'BOC', 1000, '2010-07-12 13:27:58', 'sa');
 

[code green]

GROUP BY the date and SUM the amounts should do it.
Change GROUP BY

GROUP BY U.date

Don't see why you need DISTINCT in there either

[sanjhan]

Hi ,
I really appreciate your quick response,
anyway the problem was,I have missed the 'DISTINCT'keyword in every SUM() function,I can re-write the solution as follows, which gives me the exact functionality I need.

Thank you.

SELECT DISTINCT U.date,SUM(DISTINCT I.amount)AS 'Income' ,SUM(DISTINCT A.amount) AS 'Cost' 
 
FROM 
(SELECT date from `income`GROUP BY date 
UNION 
SELECT date from `advertising`GROUP BY date 
) U 
 
LEFT JOIN `income` I 
ON U.date = I.date 
LEFT JOIN `advertising` A 
ON U.date = A.date 
 
GROUP BY U.date 

solution 2

SELECT t.date, Income, Cost
FROM
  (SELECT date FROM income
  UNION
  SELECT date FROM advertising
  ORDER BY
    date
  ) t
  LEFT JOIN (SELECT date, SUM(amount) AS 'Income' FROM  income GROUP BY date) i ON t.date = i.date
  LEFT JOIN (SELECT date, SUM(amount) AS 'Cost' FROM advertising GROUP BY date) a ON t.date = a.date;
 

solution 3

SELECT Date
     , SUM(ins) Income
     , SUM(outs) Cost
  FROM
     ( SELECT date, amount ins, NULL outs FROM income
       UNION
       SELECT date, NULL, amount FROM advertising
     ) x
 GROUP 
    BY Date;