I have a query as
select sourceIP, SUM(sourceBytes) from flows where sourceBytes > 1000000 group by sourceIP
This query would bring the IP addresses that sent more than 1 million bytes within all flows in a specific time frame, 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 the sourcebytes to appear as MB format. Do I need a nested query for that? Thanks.
Advertisement
Answer
Use this expression. CONCAT is SQL Server 2012+ and deals with datatypes
...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 output you want>