如何构建查询(sum,group by)

I have the following table

我有下面这张桌子

 #   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

where 0 means cash and 1 means credit in the type column

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

The problem is to find the total of cash usages and credit usages and total amount for every ID.

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

I'm looking for query that gets following result

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

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

I would like to use one query if it's possible

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

thanks

谢谢

3 个解决方案

#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

#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

#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