Loop in mySQL query to remove null columns-CodePudding

I am writing a query in mySQL that displays the number of attempts made against each status/disposition. Following is my query:

SELECT 
    `status` AS `STATUS`,
    COUNT(CASE called_count WHEN '0' THEN 1 ELSE NULL END) AS '0',
    COUNT(CASE called_count WHEN '1' THEN 1 ELSE NULL END) AS '1',
    COUNT(CASE called_count WHEN '2' THEN 1 ELSE NULL END) AS '2',
    COUNT(CASE called_count WHEN '3' THEN 1 ELSE NULL END) AS '3',
    COUNT(CASE called_count WHEN '4' THEN 1 ELSE NULL END) AS '4',
    COUNT(*) AS SUBTOTAL
FROM
    vicidial_list
WHERE
    list_id = '202201111' 
GROUP BY
    `status`;

There are two problems here. One, I am hard coding the results here e.g. COUNT(CASE called_count WHEN '2' THEN 1 ELSE NULL END) AS '2'. We can't be sure how many times a number will dial, so we'll not be able to cater all results this way. Secondly, even if I do, some columns are totally 0, which doesn't serve any purpose. Like here column '4' but in other cases it can even be '0' as well. So I want to optimize my query to remove the column with all 0's as well as use loop if I can. Following is my result.

STATUS       0       1       2       3       4  SUBTOTAL  
------  ------  ------  ------  ------  ------  ----------
AA           0  167112   57535    5596       0      230243
ADC          0   20024       2       0       0       20026
B            0    2054     893      93       0        3040
DROP         0       7       4       0       0          11
LRERR        0       8       5       0       0          13
NA           0  248079   43154    1543       0      292776
NEW     165982       2       0       0       0      165984
PDROP        0    3096     485      27       0        3608
PM           0    8236    2905     311       0       11452
PU           0   32819    5958     343       0       39120
XFER         0    1692     345      17       0        2054

Any help will be appreciated.

CodePudding user response：

It is the nature of SQL that the select-list is fixed at the time the query is parsed, which is before it begins reading any data. So you cannot make any SQL query that expands or reduces the columns in the select-list depending on what values it finds after it begins reading rows.

You have two choices to solve this:

Use two queries. One to find the range of values you want to display by reading your called_count column.
```
 SELECT DISTINCT called_count FROM vicidial_list WHERE list_id = '202201111';
```
Then use this result in client code to format a second SQL query with exactly the columns you want.
Don't do the pivot-table formatting in an SQL query at all. Just fetch all the raw data to your client, and implement the aggregation in the client. Present the result in a tabular manner however you want, after counting the results by status and by called_count.

Both of these solutions require you to write some code in the client.

CodePudding user response：

You can dynamically figure out how many columns to make in a SELECT, but it involves a Stored Procedure to construct the SELECT and run it.

The number of columns to make comes from SELECT MAX(called_count) ...

This provides much of the code to construct and run the "pivot" query: http://mysql.rjweb.org/doc.php/pivot

(As they say, "the rest is left as an exercise for the reader".)