如何构建该查询(sum，group )

Question

我有下表

 #   amount   type
 1     10      0
 1     10      0
 1     5       1
 1     5       1
 2     10      0
 2     10      0
 2     5       1
 2     5       1

0表示现金，1表示类型列中的信用。

问题是要找出现金使用和信贷使用的总数以及每个ID的总金额。

我在寻找得到以下结果的查询

 #   cash credit total
 1    20    10    30 
 2    20    10    30

如果可能的话，我想使用一个查询

谢谢

Answer 1

SELECT id, 
SUM(CASE WHEN type = 0 THEN amount ELSE 0 END) as "cash",
SUM(CASE WHEN type = 1 THEN amount ELSE 0 END) as "credit", 
SUM(amount) as "total"
FROM your_table
GROUP BY id

Answer 2

SELECT
  num,
  SUM(CASE WHEN type=0 THEN amount END) cash,
  SUM(CASE WHEN type=1 THEN amount END) credit,
  SUM(amount) total
FROM
  yourtable
GROUP BY num

Answer 3

select id,
       sum(amount) as "total",
       CASE WHEN type = 0 THEN amount ELSE 0 END as "cash",
       CASE WHEN type = 1 THEN amount ELSE 0 END as "credit",
  from table
 group by id