How to pivot in mysql for previous date in mysql

Question

The table structure and data is present at https://www.db-fiddle.com/f/5rKXiavsoMeQazSHwxaTVK/0

i am trying to get the percentage of all previous date range.

circle_name`|current_capacity|2020-03-16|2020-03-17|2020-03-18|

where current capacity is the max date capacity and the percentage of each date in rows.

The final out like https://www.db-fiddle.com/f/wTFejVRzQBTXZkfWjBuqq8/0

select circle_name,Subscriber as current_capacity,
Subscriber/Subs_Capacity * 100 as percentage,
DATE_FORMAT(date,'%y-%m-%d') as date
from circle 
where date BETWEEN date_add('2020-03-18',interval -3 day) 
and '2020-03-18'

Please help here in pivot in mysql.

Answer 1

Here is one approach that uses conditional aggregation. Instead of assigning actual dates to column names (which would require dynamic SQL), this works by setting a variable that represents the date from which you want the computation to start, and then generates columns named d_2 (for day - 2), d_1 and d_0.

set @mydate = '2020-03-18';
select 
    circle_name,
    max(case when date = @mydate then subscriber end) current_capacity,
    100 * sum(case when date = @mydate - interval 2 day then subscriber end)
        / sum(case when date = @mydate - interval 2 day then subs_capacity end) d_2,
    100 * sum(case when date = @mydate - interval 1 day then subscriber end)
        / sum(case when date = @mydate - interval 1 day then subs_capacity end) d_1,
    100 * sum(case when date = @mydate                  then subscriber end)
        / sum(case when date = @mydate                  then subs_capacity end) d_0
from circle
group by circle_name;

In your db fiddle, the query yields:

| circle_name | current_capacity | d_2     | d_1     | d_0     |
| ----------- | ---------------- | ------- | ------- | ------- |
| AP          | 758415           | 90.8422 | 91.1373 | 91.3753 |
| AS          | 764976           | 83.461  | 83.2508 | 82.7001 |
| BH          | 807447           | 84.5168 | 86.2083 | 85.8986 |
| CH          | 785384           | 87.4384 | 87.2934 | 87.2649 |
| DL          | 859161           | 85.9683 | 85.5766 | 85.9161 |
| GJ          | 882817           | 85.6419 | 85.7533 | 86.1285 |
| HP          | 203300           | 80.9292 | 80.9608 | 81.32   |
| HR          | 255511           | 84.9163 | 85.1213 | 85.1703 |
| JK          | 592271           | 84.244  | 84.2937 | 84.6101 |
| KK          | 729628           | 88.0607 | 88.1499 | 87.907  |
| KL          | 793872           | 80.0359 | 79.5544 | 79.3872 |
| KO          | 847638           | 84.6341 | 84.6501 | 84.7638 |
| MB          | 687501           | 87.8208 | 85.9449 | 85.9376 |
| MH          | 886554           | 95.8487 | 95.6997 | 88.6554 |
| MP          | 821474           | 83.1335 | 83.3047 | 83.8239 |
| NE          | 824807           | 87.5708 | 87.5969 | 86.8218 |
| OR          | 710822           | 84.5128 | 85.2319 | 83.6261 |
| PB          | 194300           | 88.798  | 96.5235 | 97.15   |
| RJ          | 840310           | 82.438  | 83.7309 | 84.031  |
| TN          | 725307           | 90.8855 | 90.7334 | 90.6634 |
| UE          | 903366           | 89.8577 | 90.0483 | 90.3366 |
| UW          | 729154           | 98.9529 | 87.1339 | 87.8499 |
| WB          | 0                | 0       | 0       | 0       |