时间戳

首页 > 解决方案 > 为什么我不能得到百分比计算？

sql - 为什么我不能得到百分比计算？

问题描述

这是我正在测试的脚本。

CREATE TABLE dbo.Account
( ProductCategory  VARCHAR(100),
  ProductSubCategory VARCHAR(100),
  TotalOrdersNAm int,
  TotalOrdersSAm int,
  TotalOrdersNAf int,
  TotalOrdersSAf int)

--Delete from Account

INSERT INTO dbo.Account  (ProductCategory, ProductSubCategory, TotalOrdersNAm, TotalOrdersSAm, TotalOrdersNAf, TotalOrdersSAf)
VALUES  ('001', 'A', '2', '5', '12', '20')

INSERT INTO dbo.Account  (ProductCategory, ProductSubCategory, TotalOrdersNAm, TotalOrdersSAm, TotalOrdersNAf, TotalOrdersSAf)
VALUES  ('001', 'B', '5', '5', '14', '21')

INSERT INTO dbo.Account  (ProductCategory, ProductSubCategory, TotalOrdersNAm, TotalOrdersSAm, TotalOrdersNAf, TotalOrdersSAf)
VALUES  ('001', 'C', '1', '7', '15', '17')

INSERT INTO dbo.Account  (ProductCategory, ProductSubCategory, TotalOrdersNAm, TotalOrdersSAm, TotalOrdersNAf, TotalOrdersSAf)
VALUES  ('002', 'A', '6', '8', '11', '16')

INSERT INTO dbo.Account  (ProductCategory, ProductSubCategory, TotalOrdersNAm, TotalOrdersSAm, TotalOrdersNAf, TotalOrdersSAf)
VALUES  ('002', 'B', '3', '9', '13', '19')

INSERT INTO dbo.Account  (ProductCategory, ProductSubCategory, TotalOrdersNAm, TotalOrdersSAm, TotalOrdersNAf, TotalOrdersSAf)
VALUES  ('002', 'C', '2', '6', '14', '0')

INSERT INTO dbo.Account  (ProductCategory, ProductSubCategory, TotalOrdersNAm, TotalOrdersSAm, TotalOrdersNAf, TotalOrdersSAf)
VALUES  ('003', 'A', '8', '8', '13', '21')

INSERT INTO dbo.Account  (ProductCategory, ProductSubCategory, TotalOrdersNAm, TotalOrdersSAm, TotalOrdersNAf, TotalOrdersSAf)
VALUES  ('003', 'B', '2', '9', '10', '18')

INSERT INTO dbo.Account  (ProductCategory, ProductSubCategory, TotalOrdersNAm, TotalOrdersSAm, TotalOrdersNAf, TotalOrdersSAf)
VALUES  ('003', 'C', '9', '0', '0', '29')

-- Select * From Account

SELECT t.ProductCategory, t.ProductSubCategory, u.Region, u.TotalOrders,
     u.TotalOrders / SUM(u.TotalOrders) OVER (PARTITION BY u.Region) as PercentageOfTotal
FROM dbo.Account t
CROSS APPLY
    (SELECT 'North America' as Region, TotalOrdersNAm as TotalOrders
     UNION ALL
     SELECT 'South America' as Region, TotalOrdersSAm as TotalOrders
     UNION ALL
     SELECT 'North Africa' as Region, TotalOrdersNAf as TotalOrders
     UNION ALL
     SELECT 'South Africa' as Region, TotalOrdersNAf as TotalOrders
    ) u

我在 SQL Server 2017 上。在我看来，这应该可以工作，但我在“PercentageOfTotal”字段中看到的只是 0。我在这里想念什么？

标签： sqlsql-server

解决方案

SQL Server 执行整数运算，而1/2不是. 我发现最简单的方法是乘以：00.51.0

 u.TotalOrders * 1.0 / SUM(u.TotalOrders) OVER (PARTITION BY u.Region) as PercentageOfTotal

推荐阅读