CODEWITHSUNDEEP
mysqlsql
JamesKing
JamesKing

Reputation: 33

Combine two tables unique column

I have two tables like shown as below:

mysql> select*from receipt;                                                                                
+----+------------+-------+---------------------+
| id | receipt_id | money | created_at          |
+----+------------+-------+---------------------+
|  1 | receipt_3  |   100 | 2018-10-30 00:00:00 |
|  2 | receipt_4  |   200 | 2018-10-29 00:00:00 |
|  3 | receipt_5  |   300 | 2018-10-31 00:00:00 |
+----+------------+-------+---------------------+
3 rows in set (0.00 sec)

mysql> select*from material;
+----+------------+---------------------+
| id | receipt_id | created_at          |
+----+------------+---------------------+
|  1 | receipt_3  | 2018-10-30 00:00:00 |
|  2 | receipt_3  | 2018-10-30 00:00:00 |
|  3 | receipt_5  | 2018-10-31 00:00:00 |
+----+------------+---------------------+
3 rows in set (0.00 sec)

I have got the result use this: "select distinct sum(money),material.created_at from receipt inner join material on receipt.receipt_id=material.receipt_id group by material.created_at;" 

+------------+---------------------+
| sum(money) | created_at          |
+------------+---------------------+
|        200 | 2018-10-30 00:00:00 |
|        300 | 2018-10-31 00:00:00 |
+------------+---------------------+
2 rows in set (0.00 sec)

but I want to get this with ignoring same record:

+------------+---------------------+
| sum(money) | created_at          |
+------------+---------------------+
|        100 | 2018-10-30 00:00:00 |
|        300 | 2018-10-31 00:00:00 |
+------------+---------------------+
2 rows in set (0.00 sec)

Have thought for a long time but no answer,please help ,thanks a lot.

Upvotes: 3

Views: 49

Answers (2)

D-Shih
D-Shih

Reputation: 46249

You can try to use DISTINCT for material in a subquery ignoring same record then JOIN

SELECT sum(r.money),t1.created_at
FROM (
    select DISTINCT receipt_id,created_at   
    from material
)t1 JOIN receipt r on t1.receipt_id = r.receipt_id  
GROUP BY t1.created_at

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

Just use sum(distinct money) :

select sum(distinct money) as sum_money,
       m.created_at 
  from receipt r join material m 
    on r.receipt_id=m.receipt_id 
 group by m.created_at;

+------------+---------------------+
| sum_money  | created_at          |
+------------+---------------------+
|        100 | 2018-10-30 00:00:00 |
|        300 | 2018-10-31 00:00:00 |
+------------+---------------------+

DB-Fiddle Demo

Upvotes: 2

Related Questions