SQL如何计算一个组的相对值？ [英] SQL how to calculate relative value for a group?

问题描述

假设我们销售汽车，我们想知道每个品牌销售多少辆汽车。

数据：

  car_id |品牌|卖出
 ---------------------- 
 1 |宝马|真
 2 |宝马|假
 3 |马自达|真
 4 |马自达|假
 5 |马自达| true 
  

我们希望获得的结果：

  brand |售出
 ------------ 
 BMW | 50％
马自达| 66％
  

如何做到这一点（在PostgreSQL中）？

GROUP BY ，然后结合使用 COUNT 来解决方案用 CASE 语句除以 SUM 的真或假。

假设sold包含的字符串不是真或假。

  SELECT品牌，SUM（CASE WHEN sold ='true '
 THEN 1 
 ELSE 0 
 END）/ COUNT（*）AS已售出
 FROM thedata 
 GROUP BY品牌
  
 
 
 
输出 
 
 
 品牌售罄
宝马0.5 
马自达0.66 
  
或者在SQL级别完成格式化。
  SELECT品牌，ROUND（SUM（CASE WHEN sold ='true'
 THEN 1 
 ELSE 0 
 END） / COUNT（*））* 100）|| '％'AS已售出
来自数据
 GROUP BY品牌
  
 p> 
 
 
 品牌售罄
宝马50％
马自达66％
  
 
Let's suppose we selling cars, and we would like to know how many cars are sold for each brand. 


The data:
car_id | brand | sold
----------------------
1      | BMW   | true
2      | BMW   | false
3      | Mazda | true
4      | Mazda | false
5      | Mazda | true
The result we would like to get:
brand | sold
------------
BMW   | 50%
Mazda | 66%
How to do it (in PostgreSQL)?
解决方案
Use GROUP BY and then a combination of COUNT divided by a SUM of the true or false using a CASE statement.

Assuming sold contains strings not actual true or false.
SELECT brand, SUM(CASE WHEN sold = 'true' 
                       THEN 1 
                       ELSE 0 
                  END)/COUNT(*) AS Sold
FROM thedata
GROUP BY brand
Output
brand   Sold
BMW     0.5
Mazda   0.66
Or with formatting done at SQL level.
SELECT brand, ROUND(SUM(CASE WHEN sold = 'true' 
                             THEN 1 
                             ELSE 0 
                        END)/COUNT(*))*100)|| '%' AS Sold
FROM thedata
GROUP BY brand
Output
brand   Sold
BMW     50%
Mazda   66%


                        
这篇关于SQL如何计算一个组的相对值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！