Mysql join two tables sum, where and group by

Question

I have two tables in the following structure:

sales
 |date       |time     | name   | total |
 |2017-04-01 |10:23:59 | aaa    |  100  |
 |2017-04-01 |10:23:59 | aaa    |  150  |
 |2017-04-01 |11:33:30 | bbb    |  200  |
 |2017-04-01 |11:33:30 | bbb    |  120  |
 |2017-04-02 |10:50:59 | aaa    |  70   |
 |2017-04-02 |10:30:59 | bbb    |  35   |

payment
 |date       |time     | name   | amount |
 |2017-04-01 |10:23:59 | aaa    |  300   |
 |2017-04-01 |11:33:30 | bbb    |  400   |
 |2017-04-02 |10:50:59 | aaa    |  425   |
 |2017-04-02 |10:30:59 | bbb    |  600   |

Terms

sales.time = payment.time
where date = 2017-04-01
sum(sales.total) and sum(payment.amount)
group by time

i want this result

 |date       |time     | name   | sum(total) | sum(amount)|
 |2017-04-01 |13:23:59 | aaa    |    250     |    300     |
 |2017-04-01 |12:33:30 | bbb    |    320     |    400     |

Table structure

CREATE TABLE `payment` (`id` int(5) NOT NULL,`date` date NOT NULL,`time` time NOT NULL,`name` varchar(10) NOT NULL,`amount` varchar(10) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
INSERT INTO `payment` (`id`, `date`, `time`, `name`, `amount`) VALUES(3, '2017-04-01', '10:23:59', 'aaa', '300'),(4, '2017-04-01', '11:33:30', 'bbb', '400'),(5, '2017-04-02', '10:50:59', 'aaa', '425'),(6, '2017-04-02', '10:30:59', 'bbb', '600');
CREATE TABLE `sales` ( `id` int(5) NOT NULL,`date` date NOT NULL,`time` time NOT NULL,`name` varchar(10) NOT NULL,`total` int(10) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `sales` (`id`, `date`, `time`, `name`, `total`) VALUES(1, '2017-04-01', '10:23:59', 'aaa', 100),(2, '2017-04-01', '10:23:59', 'aaa', 150),(3, '2017-04-01', '11:33:30', 'bbb', 200),(4, '2017-04-01', '11:33:30', 'bbb', 120),(5, '2017-04-02', '10:50:59', 'aaa', 70),(6, '2017-04-02', '10:50:59', 'bbb', 35);
ALTER TABLE `payment` ADD PRIMARY KEY (`id`);
ALTER TABLE `sales` ADD PRIMARY KEY (`id`);
ALTER TABLE `payment` MODIFY `id` int(5) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
ALTER TABLE `sales` MODIFY `id` int(5) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

Query

SELECT sales.time,sales.name,  
SUM(sales.total),SUM(payment.amount)
FROM sales,payment  
WHERE sales.time=payment.time
and sales.date like '2017-04-01%'
GROUP BY sales.time
ORDER BY sales.time;

Result is

10:23:59    aaa 250 600
11:33:30    bbb 320 800

You should be able to put those conditions together into a query. — Gordon Linoff, Apr 15 '17 at 13:46
Edit your question and show the query you have tried, and explain why the results are not correct. — Gordon Linoff, Apr 15 '17 at 13:56

score 2 · Accepted Answer · answered Apr 15 '17 at 14:16

You are multiplying the payments amount with the number of sales records, because you are joining all payments records with all sales records before summing up the amounts.

Aggregate first, and only join then.

In case there can always only be one payments record per date, time and name:

select p.name, p.time, p.name, s.sales_total, p.amount
from payments p
join
(
  select date, time, name, sum(total) as total
  from sales
  group by date, time, name
) s
 on s.date = p.date and s.time = p.time and s.name = p.name
where p.date = date '2017-04-01';

Otherwise:

select p.name, p.time, p.name, s.total, p.amount
(
  select date, time, name, sum(amount) as amount
  from payments
  group by date, time, name
) p
join
(
  select date, time, name, sum(total) as total
  from sales
  group by date, time, name
) s
 on s.date = p.date and s.time = p.time and s.name = p.name
where p.date = date '2017-04-01';

just work fine result ok Thanks :-) change table name – A. Sang Apr 15 '17 at 14:24 — A. Sang, Apr 15 '17 at 14:24
group by on payments table ERROR group by date' at line 3 – A. Sang Apr 15 '17 at 14:45 — A. Sang, Apr 15 '17 at 14:45

Mysql join two tables sum, where and group by

1 Answers1

Linked