MySQL Formatting Query - Install Column Results as Table Header
I am trying to create a report in MySQL to display the number of stocks traded, by client and by market.
I have the results I want (using multiple tables), but need to modify the output table so that the results from one column become the header of the other, and then the quantity for the customer by market.
This is what I still have;
mysql> SELECT IFNULL(sc.name,"All Clients") as "Clients",
IFNULL(o.exchangeid,"All Markets") as "Markets",
SUM(o.filledqty) as "Total Shares"
from ixrisk.orders o, ixrisk.clients c, ixrisk.sub_clients sc
where c.sub_client_id = sc.id and o.compid = c.clientname
GROUP BY sc.name, o.exchangeid WITH ROLLUP;
+------------------+-------------+--------------+
| Clients | Markets | Total Shares |
+------------------+-------------+--------------+
| FIXN_CL_CLIENT_1 | AMS | 70 |
| FIXN_CL_CLIENT_1 | BTE | 112 |
| FIXN_CL_CLIENT_1 | LSE | 1147 |
| FIXN_CL_CLIENT_1 | All Markets | 1329 |
| qa_client_tst1 | LSE | 0 |
| qa_client_tst1 | All Markets | 0 |
| All Clients | All Markets | 1329 |
+------------------+-------------+--------------+
7 rows in set (0.01 sec)
I wish it looked like this, but it didn't work.
+------------------+------+------+------+-------------+
| Clients | AMS | BTE | LSE | All Markets |
+------------------+------+------+------+-------------+
| FIXN_CL_CLIENT_1 | 70 | 112 | 1147 | 1329 |
| qa_client_tst1 | 0 | 0 | 0 | 0 |
| All Clients | 70 | 112 | 1147 | 1329 |
+------------------+------+------+------+-------------+
3 rows in set (0.01 sec)
I looked through similar threads and tried "inner joins" and "pivots" but couldn't get them to work with my other options.
Can anyone please help?
UPDATE I modified the MySQL script to get the format I want now, but each client should have only one row with all Exchange values. I have tried various combinations and can get one row, but the values are not filling in correctly.
source to share
mysql> select sc.name,
COALESCE(SUM(case when `exchangeid` = 'AMS' then o.filledqty end),0) as AMS,
COALESCE(SUM(case when `exchangeid` = 'BRU' then o.filledqty end),0) as BRU,
COALESCE(SUM(case when `exchangeid` = 'BTE' then o.filledqty end),0) as BTE,
COALESCE(SUM(case when `exchangeid` = 'CHI' then o.filledqty end),0) as CHI,
COALESCE(SUM(case when `exchangeid` = 'CPH' then o.filledqty end),0) as CPH,
COALESCE(SUM(case when `exchangeid` = 'GER' then o.filledqty end),0) as GER,
COALESCE(SUM(case when `exchangeid` = 'HEX' then o.filledqty end),0) as HEX,
COALESCE(SUM(case when `exchangeid` = 'JNB' then o.filledqty end),0) as JNB,
COALESCE(SUM(case when `exchangeid` = 'LIS' then o.filledqty end),0) as LIS,
COALESCE(SUM(case when `exchangeid` = 'LSE' then o.filledqty end),0) as LSE,
COALESCE(SUM(case when `exchangeid` = 'MCE' then o.filledqty end),0) as MCE,
COALESCE(SUM(case when `exchangeid` = 'MIL' then o.filledqty end),0) as MIL,
COALESCE(SUM(case when `exchangeid` = 'OSL' then o.filledqty end),0) as OSL,
COALESCE(SUM(case when `exchangeid` = 'PAR' then o.filledqty end),0) as PAR,
COALESCE(SUM(case when `exchangeid` = 'STO' then o.filledqty end),0) as STO,
COALESCE(SUM(case when `exchangeid` = 'SWX' then o.filledqty end),0) as SWX,
COALESCE(SUM(case when `exchangeid` = 'TRQ' then o.filledqty end),0) as TRQ,
COALESCE(SUM(case when `exchangeid` = 'VIE' then o.filledqty end),0) as VIE,
COALESCE(SUM(case when `exchangeid` = 'VTX' then o.filledqty end),0) as VTX,
COALESCE(SUM(o.filledqty),0) as "All Markets"
from orders o
INNER JOIN clients c on o.compid = c.clientname
INNER JOIN sub_clients sc on c.sub_client_id = sc.id
INNER JOIN sub_clients sc on c.sub_client_id = sc.id
group by sc.name;
source to share