CODEWITHSUNDEEP
mysqlmariadb
TFC
TFC

Reputation: 65

How can I get max value of two column in manpulated table in MySQL

I have a table and I manipulated it to cretae some graph. Here is original table

    select * from traffic_user_daily;
    +-------------------+------------+-------+---------+----------+
    | username          | cdate      | chour | bytesin | bytesout |
    +-------------------+------------+-------+---------+----------+
    | 00:09:df:89:81:f2 | 2016-05-27 | 16    |   16469 |    10321 |
    | 00:09:df:89:81:f2 | 2016-05-27 | 18    |  599161 |   121796 |
    | 00:09:df:89:81:f2 | 2016-05-27 | 19    |   21058 |    11141 |
    | 00:09:df:89:81:f2 | 2016-05-28 | 08    |   20608 |    72061 |
    | 00:09:df:89:81:f2 | 2016-05-28 | 09    |  359375 |   176809 |

And I run sıme query like this:

    SELECT CONCAT(cdate,' ',chour,':00:00') AS ctime ,sum(bytesin*0.000002222) as totalKbpsin, sum(bytesout*0.000002222) as totalKbpsout FROM traffic_user_daily group by cdate,chour HAVING ctime >= now() - INTERVAL 1 DAY

    +---------------------+----------------+----------------+
    | ctime               | totalKbpsin    | totalKbpsout   |
    +---------------------+----------------+----------------+
    | 2016-05-31 10:00:00 | 1230.390102018 |  772.301406580 |
    | 2016-05-31 11:00:00 | 2554.907603688 | 1185.870290000 |
    | 2016-05-31 12:00:00 | 2260.437353658 |  295.407289650 |
    | 2016-05-31 13:00:00 | 3031.315984752 |  316.283035200 |
    | 2016-05-31 14:00:00 | 3872.374530486 |  720.599486178 |

But dont know how to get MAX values of totalKbpsin and totalKbpsout columns. Cand you help me? Regards,

Upvotes: 0

Views: 51

Answers (4)

TFC
TFC

Reputation: 65

$query_IO ="

SELECT  MAX(totalKbpsin),MAX(totalKbpsout)
    FROM  
      ( SELECT  CONCAT(cdate,' ',chour,':00:00') AS ctime ,sum(bytesin*0.000002222) as totalKbpsin,
                sum(bytesout*0.000002222) as totalKbpsout
            FROM  traffic_user_daily
            group by  cdate,chour
            HAVING  ctime >= now() - INTERVAL 1 DAY
      ) a

";

solved my problem. Thnaks for your assistance...

Upvotes: 0

TFC
TFC

Reputation: 65

I solved it . 

$query_IO ="SELECT MAX(totalKbpsin),MAX(totalKbpsout) FROM (SELECT CONCAT(cdate,' ',chour,':00:00') AS ctime ,sum(bytesin*0.000002222) as totalKbpsin, sum(bytesout*0.000002222) as totalKbpsout FROM traffic_user_daily group by cdate,chour HAVING ctime >= now() - INTERVAL 1 DAY) a ";

Upvotes: 0

Abhinaw Kumar
Abhinaw Kumar

Reputation: 132

You can use GREATEST() function.

select GREATEST(MAX(totalKbpsin),MAX(totalKbpsout)) from table_a

Upvotes: 1

Blank
Blank

Reputation: 12378

With your sample data, please try this;)

SELECT main.ctime, main.totalKbpsin, main.totalKbpsout
FROM(
    SELECT CONCAT(cdate,' ',chour,':00:00') AS ctime, SUM(bytesin*0.000002222) AS totalKbpsin, SUM(bytesout*0.000002222) AS totalKbpsout
    FROM traffic_user_daily
    GROUP BY cdate,chour
    HAVING ctime >= NOW() - INTERVAL 1 DAY) main
INNER JOIN (
    SELECT MAX(totalKbpsin) AS totalKbpsin
    FROM (
        SELECT CONCAT(cdate,' ',chour,':00:00') AS ctime, SUM(bytesin*0.000002222) AS totalKbpsin, SUM(bytesout*0.000002222) AS totalKbpsout
        FROM traffic_user_daily
        GROUP BY cdate,chour
        HAVING ctime >= NOW() - INTERVAL 1 DAY) tmp
) maxsin ON main.totalKbpsin = maxsin.totalKbpsin
UNION ALL
SELECT main.ctime, main.totalKbpsin, main.totalKbpsout
FROM(
    SELECT CONCAT(cdate,' ',chour,':00:00') AS ctime, SUM(bytesin*0.000002222) AS totalKbpsin, SUM(bytesout*0.000002222) AS totalKbpsout
    FROM traffic_user_daily
    GROUP BY cdate,chour
    HAVING ctime >= NOW() - INTERVAL 1 DAY) main
INNER JOIN (
    SELECT MAX(totalKbpsout) AS totalKbpsout
    FROM (
        SELECT CONCAT(cdate,' ',chour,':00:00') AS ctime, SUM(bytesin*0.000002222) AS totalKbpsin, SUM(bytesout*0.000002222) AS totalKbpsout
        FROM traffic_user_daily
        GROUP BY cdate,chour
        HAVING ctime >= NOW() - INTERVAL 1 DAY) tmp
) maxsout ON maxsout.totalKbpsout = main.totalKbpsout

Upvotes: 1

Related Questions