Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sql query to convert or display bytes columns into MB?

Tags:

syntax

sql

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.

like image 541
user3066819 Avatar asked Aug 07 '14 07:08

user3066819


Video Answer


1 Answers

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>

like image 134
gbn Avatar answered Nov 02 '22 23:11

gbn