MYSQL Query, Horizontally with avg and sum

Question

I need to take these rows and output horizontally with the Dates as the column headers. I have looked at other solutions but I am a newbie and cant figure it out.

     ENV      | SUM_TRX | TRX_DATE
    ------------------------------------------
    mfg_Dev   | 392     |  2018-05-01
    mfg_Dev   | 2848    |  2018-05-02
    mfg_Dev   | 4024    |  2018-05-03
    mfg_Dev   | 92261   |  2018-05-04
    mfg_Dev   | 428     |  2018-05-05
    mfg_Dev   | 406     |  2018-05-06
    mfg_QA    | 278134  |  2018-05-01
    mfg_QA    | 485122  |  2018-05-02
    mfg_QA    | 882138  |  2018-05-03
    mfg_QA    | 1207312 |  2018-05-04
    mfg_QA    | 1258550 |  2018-05-05
    mfg_QA    | 981031  |  2018-05-06
    mfg_Stress| 0       |  2018-05-01
    mfg_Stress| 4       |  2018-05-02
    mfg_Stress| 1       |  2018-05-03
    mfg_Stress| 6       |  2018-05-04
    mfg_Stress| 0       |  2018-05-05
    mfg_Stress| 0       |  2018-05-05
    mfg_Prod  | 60943069|  2018-05-01
    mfg_Prod  | 53060886|  2018-05-02
    mfg_Prod  | 52098890|  2018-05-03
    mfg_Prod  | 49489239|  2018-05-04
    mfg_Prod  | 19044338|  2018-05-05
    mfg_Prod  | 20569390|  2018-05-06

To:

    MFG         |   18-05-01 |  2018-05-02  | 2018-05-02     | 2018-05-04   |  2018-05-05 | 2018-05-06  | Avg count day
    ----------------------------------------------------------------------------------------------------------------
    mfg_Dev     |       392  |      2,848   |       92,261   |     92,261   |         428 |        406  |     16,055
    mfg_QA      |   278,134  |    485,122   |      882,138   |  1,258,550   |   1,207,312 |  1,258,550  |    894,967
    mfg_Stress  |         0  |          4   |            1   |          6   |           0 |          0  |          2
    mfg_Prod    | 60,943,069 | 53,060,886   |   52,098,890   | 49,489,239   |  19,044,338 | 20,569,390  | 42,534,302
    -----------------------------------------------------------------------------------------------------------------
          TOTAL:| 61,221,595 | 53,548,860   |   53,073,290   | 50,840,056   |  20,252,078 | 21,828,346

Can anyone help with a query?

Thanks in Advance!

Please reopen, I need help disparately!

https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Shankar Duvvuri, May 14 '18 at 20:32
Seriously consider handling issues of data display in application code — Strawberry, May 14 '18 at 20:41

score 0 · Answer 1 · answered May 14 '18 at 23:44

Consider this crude example...

<?php

//include('path/to/connection/statme.nts');

$query = "
SELECT env
     , sum_trx
     , trx_date
  FROM my_table
 ORDER
    BY env,trx_date;
";

$result = mysqli_query($conn,$query);

$array = array();

while($row = mysqli_fetch_assoc($result)){
$array[] = $row;
}

foreach($array as $v){
$new_array[$v['env']][$v['trx_date']] = $v['sum_trx'];
}


print_r($new_array);
?>

Outputs...

Array
(
    [Dev] => Array
        (
            [2018-05-01] => 392
            [2018-05-02] => 2848
            [2018-05-03] => 4024
            [2018-05-04] => 92261
            [2018-05-05] => 428
            [2018-05-06] => 406
        )

    [Prod] => Array
        (
            [2018-05-01] => 60943069
            [2018-05-02] => 53060886
            [2018-05-03] => 52098890
            [2018-05-04] => 49489239
            [2018-05-05] => 19044338
            [2018-05-06] => 20569390
        )

    [QA] => Array
        (
            [2018-05-01] => 278134
            [2018-05-02] => 485122
            [2018-05-03] => 882138
            [2018-05-04] => 1207312
            [2018-05-05] => 1258550
            [2018-05-06] => 981031
        )

    [Stress] => Array
        (
            [2018-05-01] => 0
            [2018-05-02] => 4
            [2018-05-03] => 1
            [2018-05-04] => 6
            [2018-05-05] => 0
            [2018-05-06] => 0
        )

)

Thank you @Strawberry. That helps! – user2604686 May 16 '18 at 14:44 — user2604686, May 16 '18 at 14:44

MYSQL Query, Horizontally with avg and sum

1 Answers1