mysql - 在mySQL select中将两个计算值相除

Question

这是我的查询:

SELECT  a.cartodb_id as cartdb_id, a.identifier AS store,
(SELECT count(*) FROM full_data_for_testing_deid_2) ,count(b.*) AS   customer_count 
FROM demo_locations_table a, full_data_for_testing_deid_2 b 
WHERE ST_DWithin(a.the_geom::geography,b.the_geom::geography,16093) 
GROUP BY a.cartodb_id

返回结果如下:

| cartdb_id | store           | count | customer_count |
|-----------|-----------------|-------|----------------|
| 1         | Store Number: 1 | 32085 | 30             |
| 2         | Store Number: 2 | 32085 | 283            |
| 3         | Store Number: 3 | 32085 | 609            |
| 4         | Store Number: 4 | 32085 | 61             |
| 5         | Store Number: 5 | 32085 | 325            |

在本例中，count 等于表中的客户总数(每个客户一行)。我想要的是另一列显示每个商店的客户百分比:customer_count 除以 count，但我不知道如何将其集成到我的查询中。 PS:我实际上不需要 count 列。我只是将其作为引用。

谢谢。

Answer 1

SELECT  a.cartodb_id as cartdb_id, a.identifier AS store,
count(b.*) AS   customer_count , count(b.*)/(SELECT count(*) FROM full_data_for_testing_deid_2)*100 as Percentage
FROM demo_locations_table a, full_data_for_testing_deid_2 b 
WHERE ST_DWithin(a.the_geom::geography,b.the_geom::geography,16093) 
GROUP BY a.cartodb_id