Sql query to convert or map byte columns to MB?
I have a request like
select
sourceIP, SUM(sourceBytes)
from
flows
where
sourceBytes > 1000000
group by
sourceIP
This request will result in IP addresses that have sent over 1 million bytes in all streams in a given period of time, you must enter
The result is similar to
------------------------------------
| sourceIP | SUM_sourceBytes |
------------------------------------
| 64.124.201.151 | 4282590.0 |
| 10.105.2.10 | 4902509.0 |
| 10.103.70.243 | 2802715.0 |
| 10.103.77.143 | 3313370.0 |
| 10.105.32.29 | 2467183.0 |
| 10.105.96.148 | 8325356.0 |
| 10.103.73.206 | 1629768.0 |
------------------------------------
I want sourcebytes to appear as MB format. Do I need a nested query? Thank.
+3
source to share
1 answer
Use this expression. CONCAT is SQL Server 2012+ and deals with data types
...CONCAT(SUM(sourceBytes) / 1048576.0, 'MB')..
Example:
SELECT
sourceIP, CONCAT(SUM(sourceBytes) / 1048576.0, 'MB')
FROM
(VALUES ('10.105.2.10', 4902509.0), ('10.103.70.243', 2802715.0))
AS flows (sourceIP, sourceBytes)
WHERE
sourceBytes > 1000000
GROUP BY
sourceIP;
Otherwise, what is the expected result you want>
+6
source to share