SQL：将呼入时长/天相加，将同表中的呼出时长/天相加

Question

我快疯了...我无法将两个COLLUM求和得到一个包含每天来电持续时间和去话持续时间之和的resulttable。

我的电话桌是这样的

|date       |duration    |direction|number       |time    |device |
|2021-04-1  |2           |1        |123456       |01:00   |phone1 |
|2021-04-1  |10          |2        |789012       |01:15   |phone2 |
|2021-04-2  |25          |1        |123456       |11:00   |phone1 |
|2021-04-2  |10          |2        |789012       |12:15   |phone2 |
...
...

我的结果应该是这样的

|date       |intotal    |outtotal|
|2021-04-1  |2          |10      |      
|2021-04-2  |25         |10      | 
...
...

目前，我使用3个视图（伪代码）

View 1. select date, sum(duration) from calls where direction = 1 group by date
View 2. select date, sum(duration) from calls where direction = 2 group by date
View 3. select out.date AS date,in.duration AS intotal, out.duration AS outtotal
                 from view1 as out left join view2 as in on (out.date = in.date) 
//and a union right join with the same statement if there is no in or outcoming call on a day

但这不可能是最终的解决办法...只要一个声明就可以了。谁能给我一个正确方向的提示吗？

（我正在运行一个MariaDB 10.1.47）

Answer 1

如果我没有理解错，您想要条件聚合：

select date,
       sum(case when direct = 1 then duration end) as in_duration,
       sum(case when direct = 2 then duration end) as out_duration
from calltable
group by date;