将视图中两个计算字段的值相加，形成第三个计算列

Question

我正在为我们的移动设备管理系统开发一个视图，该系统以字节为单位收集蜂窝数据的使用情况。我有一个CONVERT函数，它将下载和上传从字节转换为GB，作为两个额外的列。没有total列，我不需要以字节为单位的total，但我需要的是以GB为单位的total。所以我试图将计算出的"；下载(GB("；以及"；上传(GB("；对于每一行。

View的SQL代码如下(我对它进行了格式化，希望它看起来更可读(

SELECT
dbo.DevInfo.DevId,
dbo.DevInfo.DevName,
dbo.AndroidWorkProduct.Title,
dbo.DeviceStatNetTraffic.Application,
dbo.DeviceStatNetTraffic.TimeStamp,
dbo.DeviceStatNetTraffic.Upload,
dbo.DeviceStatNetTraffic.Download,
dbo.DevInfo.LastCheckInTime,
dbo.Person.LoginName,
dbo.Person.FirstName,
dbo.Person.MiddleName,
dbo.Person.LastName,
dbo.DeviceGroup.Name,
dbo.DevInfo.Model,
dbo.DevInfo.Manufacturer,
dbo.DevInfo.OSVersion,
dbo.DevInfo.PhoneNumber,
dbo.Person.CustomProperty1,
dbo.Person.CustomProperty2,
CONVERT(DECIMAL(10, 2), dbo.DeviceStatNetTraffic.Upload / 1024 / 1024 / 1024) AS [Upload (GB)],
CONVERT(DECIMAL(10, 2), dbo.DeviceStatNetTraffic.Download / 1024 / 1024 / 1024) AS [Download (GB)]
FROM           
dbo.DeviceStatNetTraffic
WITH (NOLOCK) INNER JOIN dbo.DevInfo WITH (NOLOCK) ON dbo.DeviceStatNetTraffic.DeviceId = dbo.DevInfo.DeviceId
INNER JOIN dbo.Person ON dbo.DevInfo.CurrentPersonId = dbo.Person.PersonId
INNER JOIN dbo.DeviceGroupDevice ON dbo.DevInfo.DeviceId = dbo.DeviceGroupDevice.DeviceId
INNER JOIN dbo.DeviceGroup ON dbo.DeviceGroupDevice.DeviceGroupId = dbo.DeviceGroup.DeviceGroupId
INNER JOIN dbo.AndroidWorkProduct ON dbo.DeviceStatNetTraffic.Application = dbo.AndroidWorkProduct.AndroidProductId

执行转换的线路有：

CONVERT(DECIMAL(10, 2), dbo.DeviceStatNetTraffic.Upload / 1024 / 1024 / 1024) AS [Upload (GB)],
CONVERT(DECIMAL(10, 2), dbo.DeviceStatNetTraffic.Download / 1024 / 1024 / 1024) AS [Download (GB)]

我试过这样的东西：

SUM('Upload (GB)' + 'Download (GB)') AS [Total (GB)]
SUM(dbo.custom_datausage.'Upload (GB)' + dbo.custom_datausage.'Download (GB)') AS [Total (GB)]

但两者都不起作用。我已经做了一些关于将2个计算字段求和或相加的调查，但我真的无法理解，因为SQL不是我的专业领域，也不是我的日常工作。此外，这些示例看起来更适合正在运行的查询，而不是其他系统将从中提取的视图。

如果您能获得上传(GB(、下载(GB(和总计(GB(列集，我们将不胜感激。

以下是删除任何PII(个人信息(后的当前输出示例：

+----------------------------------------------------+------------------------------+-------------------------+------------+------------+-------------------------+-----------+--------------+-----------+-------------+---------------+
|                       Title                        |         Application          |        TimeStamp        |   Upload   |  Download  |     LastCheckInTime     |   Model   | Manufacturer | OSVersion | Upload (GB) | Download (GB) |
+----------------------------------------------------+------------------------------+-------------------------+------------+------------+-------------------------+-----------+--------------+-----------+-------------+---------------+
| Microsoft SharePoint                               | com.microsoft.sharepoint     | 2020-11-09 12:00:35.263 |   28942282 | 2926094789 | 2021-04-22 09:48:05.000 | SM-T515   | samsung      |        10 | 0.00        | 2.00          |
| Microsoft SharePoint                               | com.microsoft.sharepoint     | 2020-11-06 14:00:01.137 |   16001723 | 2494834025 | 2021-04-22 09:50:03.000 | SM-T515   | samsung      |        10 | 0.00        | 2.00          |
| Google Chrome: Fast & Secure                       | com.android.chrome           | 2020-11-20 18:00:00.777 |    7337215 | 2725987798 | 2021-04-22 03:47:28.000 | SM-A515U  | samsung      |        10 | 0.00        | 2.00          |
| Google Chrome: Fast & Secure                       | com.android.chrome           | 2020-12-15 18:00:59.257 |  529663816 | 2652521085 | 2021-04-22 09:49:36.000 | SM-A715F  | samsung      |        10 | 0.00        | 2.00          |
| Microsoft Outlook: Secure email, calendars & files | com.microsoft.office.outlook | 2021-02-10 22:00:19.053 |  524582841 | 3007983510 | 2021-04-22 09:34:35.000 | SM-J330FN | samsung      |         9 | 0.00        | 2.00          |
| ZOOM Cloud Meetings                                | us.zoom.videomeetings        | 2021-02-15 12:00:41.577 | 1438142679 | 2300425438 | 2021-04-22 09:46:07.000 | SM-A705FN | samsung      |        10 | 1.00        | 2.00          |
| Google Chrome: Fast & Secure                       | com.android.chrome           | 2021-02-15 12:00:41.597 |  131430179 | 2110910408 | 2021-04-22 09:46:07.000 | SM-A705FN | samsung      |        10 | 0.00        | 1.00          |
| Google Chrome: Fast & Secure                       | com.android.chrome           | 2020-11-17 18:00:01.670 |   16605803 | 2496396859 | 2021-04-22 09:50:25.000 | SM-A515U  | samsung      |        10 | 0.00        | 2.00          |
+----------------------------------------------------+------------------------------+-------------------------+------------+------------+-------------------------+-----------+--------------+-----------+-------------+---------------+

Answer 1

您需要将字段转换为十进制，然后除以1024 3倍

declare @bytes int = 131430179 
select convert(decimal, @bytes)/1024/1024/1024 as GB

您在对整数字段进行除法后转换数据，因此会失去精度。

输出：

0.1224038926877929 GB

Answer 2

SQL Server执行整数除法，因此1 / 2是0而不是0.5。将结果转换为十进制并不能解决此问题。

解决此问题的最简单方法是插入小数点：

CONVERT(DECIMAL(10, 2), dbo.DeviceStatNetTraffic.Upload / 1024.0 / 1024.0 / 1024.0) AS [Upload (GB)],

请注意，您应该学会使用表别名，这样您的查询更容易阅读：

CONVERT(DECIMAL(10, 2), dst.Upload / 1024.0 / 1024.0 / 1024.0) AS [Upload (GB)],

Answer 3

如果您想为每一行创建一个Total Column(Upload+Download(，您需要按列求和，而不是按您声明的别名求和。

当您使用SUM((时，您需要使用GROUP BY子句，因为这将对所有行的值求和(没有多大意义，因为您可以在查询之外对这些值求和(。

尝试以字节为单位对这两个值求和，以便转换为Gb:

如果你想为每一行创建一个Total Column(Upload+Download(，你需要按列来求和，而不是按你声明的别名。

当您使用SUM((时，您需要使用GROUP BY子句，因为这将对所有行的值求和(没有多大意义，因为您可以在查询之外对这些值求和(。

尝试以字节为单位对这两个值求和，以便转换为Gb:

SELECT
dbo.DevInfo.DevId,
dbo.DevInfo.DevName,
dbo.AndroidWorkProduct.Title,
dbo.DeviceStatNetTraffic.Application,
dbo.DeviceStatNetTraffic.TimeStamp,
dbo.DeviceStatNetTraffic.Upload,
dbo.DeviceStatNetTraffic.Download,
dbo.DevInfo.LastCheckInTime,
dbo.Person.LoginName,
dbo.Person.FirstName,
dbo.Person.MiddleName,
dbo.Person.LastName,
dbo.DeviceGroup.Name,
dbo.DevInfo.Model,
dbo.DevInfo.Manufacturer,
dbo.DevInfo.OSVersion,
dbo.DevInfo.PhoneNumber,
dbo.Person.CustomProperty1,
dbo.Person.CustomProperty2,
CONVERT(DECIMAL(10, 2), dbo.DeviceStatNetTraffic.Upload / 1024 / 1024 / 1024) AS [Upload (GB)],
CONVERT(DECIMAL(10, 2), dbo.DeviceStatNetTraffic.Download / 1024 / 1024 / 1024) AS [Download (GB)],
CONVERT(DECIMAL(10,2), (dbo.DeviceStatNetTraffic.Upload + dbo.DeviceStatNetTraffic.Download) / 1024 / 1024 / 1024) AS [Total (Gb)]
FROM           
dbo.DeviceStatNetTraffic WITH (NOLOCK) 
INNER JOIN 
dbo.DevInfo WITH (NOLOCK) ON dbo.DeviceStatNetTraffic.DeviceId = dbo.DevInfo.DeviceId
INNER JOIN 
dbo.Person ON dbo.DevInfo.CurrentPersonId = dbo.Person.PersonId
INNER JOIN 
dbo.DeviceGroupDevice ON dbo.DevInfo.DeviceId = dbo.DeviceGroupDevice.DeviceId
INNER JOIN 
dbo.DeviceGroup ON dbo.DeviceGroupDevice.DeviceGroupId = dbo.DeviceGroup.DeviceGroupId
INNER JOIN 
dbo.AndroidWorkProduct ON dbo.DeviceStatNetTraffic.Application = dbo.AndroidWorkProduct.AndroidProductId