MYSQL removing nulls from each column

Question

I'm attempting to create a sp that profiles data in a table by retrieving counts for every value in every field. The issue I'm running into is that it builds a profile table by doing a series of unions. My current results look like Result 1 below.

My question is: Is there a select statement I can use to make the results look like Result 2 or possibly a better way for me to build the table instead of using unions?

Result 1

Col1      Col1_cnt   Col2   Col2_cnt    Col3    Col3_cnt
Family     15       <NULL>  <NULL>     <NULL>   <NULL>
Friend      4       <NULL>  <NULL>     <NULL>   <NULL>
Coworker    3       <NULL>  <NULL>     <NULL>   <NULL>
<NULL>    <NULL>    <NULL>    23       <NULL>   <NULL>
<NULL>    <NULL>    John      15       <NULL>   <NULL>
<NULL>    <NULL>    Jane       4       <NULL>   <NULL>
<NULL>    <NULL>    <NULL>  <NULL>     <NULL>     8
<NULL>    <NULL>    <NULL>  <NULL>     Work       3
<NULL>    <NULL>    <NULL>  <NULL>     School     2
<NULL>    <NULL>    <NULL>  <NULL>     Social     1
<NULL>    <NULL>    <NULL>  <NULL>     Conf       5

Desired Result 2

Col1    Col1_cnt    Col2    Col2_cnt    Col3    Col3_cnt
Family    15        <NULL>    23        <NULL>    8
Friend     4        John      15        Work      3
Coworker   3        Jane       4        School    2
<NULL>   <NULL>     <NULL>   <NULL>     Social    1
<NULL>   <NULL>     <NULL>   <NULL>     Conf      5

Here is the sp I created:

DROP PROCEDURE IF EXISTS my_db.usp_profile_data;
DELIMITER $$
CREATE PROCEDURE my_db.usp_profile_data(IN vdb_name VARCHAR(255), IN vtbl_name VARCHAR(255))

BEGIN
#CREATES TEMP TABLE TO HOUSE SCHEMA DATA
DROP TABLE IF EXISTS my_db.profile_data;
CREATE TEMPORARY TABLE my_db.profile_data
(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    #ord_position INT,
    col_name VARCHAR(255),
    col_type VARCHAR(255));

#INSERTS COLUMNS OF DB AND TABLE PASSED EXCLUDING SYS FIELDS
INSERT INTO my_db.profile_data(col_name, col_type)
SELECT COLUMN_NAME, 
    COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = vdb_name
AND table_name = vtbl_name
AND EXTRA <> ('auto_increment')
AND COLUMN_NAME NOT IN('update_dt','credate_dt');

#DROP PROFILE TABLE IF ALREADY EXISTS
DROP TABLE IF EXISTS my_db.profile_data_FIN;

#INITIALIZES LOOP VARIABLE
SELECT COUNT(*)
FROM  my_db.profile_data
INTO @vLoop;

#INITIALIZE INCREMENT VARIABLE
SET @vInc =1;

#BEGIN LOOP THROUGH FIELDS
WHILE @vInc <= (@vLoop) DO


   #STORES COLUMN NAME AND DATA TYPE TO BE USED IN CREATING THE PROFILE TABLE
   SELECT col_name,
                col_type
    FROM my_db.profile_data
    WHERE id = @vInc
    INTO @vColName,@vColType;

    #IF FIRST PASS CREATE THE TABLE, ELSE ADD COLUMNS
    IF (@vInc = 1) THEN 
        SET @vSQL_Tbl = CONCAT('CREATE TEMPORARY TABLE my_db.profile_data_FIN (',@vColName,' ',@vColType,', cnt_',@vColName,' INT)');
    ELSE
        SET @vSQL_Tbl = CONCAT('ALTER TABLE my_db.profile_data_FIN ADD COLUMN ',@vColName,' ',@vColType,', ADD COLUMN cnt_',@vColName,' INT');
    END IF;

    #EXECUTES THE DYNAMIC TABLE STATEMENT
     PREPARE stmtTbl FROM @vSQL_Tbl;

        EXECUTE stmtTbl;

    DEALLOCATE PREPARE stmtTbl;

    #CREATES SQL QUERY USED TO POPULATE THE PROFILE TABLE
    SET @vSQL = CONCAT('INSERT INTO my_db.profile_data_FIN (', @vColName,', cnt_',@vColName,')', 'SELECT ',@vColName,', COUNT(*) AS cnt_',@vColName,' FROM ',vdb_name ,'.', vtbl_name,' GROUP BY ',  @vColName, ' ORDER BY 2 DESC');

   #EXECUTES THE POPULATION OF THE PROFILE TABLE
   PREPARE stmtQry FROM @vSQL;

        EXECUTE stmtQry;

    DEALLOCATE PREPARE stmtQry;

    #SELECT @vSQL;

    SET @vInc =@vInc+1;

END WHILE;


SELECT *
FROM my_db.profile_data_FIN;

END$$
DELIMITER ;

This question is not well-phrased. What logic says that the "23" should go into the "Family" record, and the "15" into the "Friend" record. I agree with @mseifert, you should share your source schema. Also, NULL is not a count, so the data doesn't match your description. — cliffordheath
– cliffordheath, Commented Oct 9, 2015 at 4:59
I've added the sp I'm using. And there technically is no logic for it to match w/ "family." It is simply removing any values above that have a NULL count. Also, NULL is absolutely a count...so I'm not sure what you mean. Using Col2 for example: What it is reporting is that a NULL value shows up in that field 23 times. — user3998848
– user3998848, Commented Oct 9, 2015 at 5:40
That was result set. And Its confused because result 2 no makes sense. Can you provide the table structure and sample data instead? — Hotdin Gurning
– Hotdin Gurning, Commented Oct 9, 2015 at 5:46
Fair enough, When I was referring to the SELECT statement that returns the result set. It will take some time for me to build a table w/ sample data but in the meantime. If you copied the sp and did a replace all on my_db to any local db you have. The database name isn't entirely dynamic yet. You can simply call the sp to see how it profiles your data. CALL my_db.usp_profile_data('my_db','YourTableName') — user3998848
– user3998848, Commented Oct 9, 2015 at 5:58

Zafar Malik · Accepted Answer · 2015-10-09 05:50:15Z

1

Try below query-

You can check sqlfiddle here.

SELECT a.s_no,a.col1,a.col1_cnt,b.col2,b.col2_cnt,c.col3,c.col3_cnt FROM 
(SELECT a.s_no,b.col1,b.col1_cnt FROM (SELECT 1 AS s_no UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a LEFT JOIN (SELECT @n := @n + 1 AS s_no1,col1,col1_cnt FROM mytable a JOIN (SELECT @n := 0) m WHERE col1_cnt IS NOT NULL) b ON a.s_no=b.s_no1) a 
LEFT JOIN (SELECT @n2 := @n2 + 1 AS s_no,col2,col2_cnt FROM mytable a JOIN (SELECT @n2 := 0) m2 WHERE col2_cnt IS NOT NULL) b
ON a.s_no=b.s_no 
LEFT JOIN (SELECT @n3 := @n3 + 1 AS s_no,col3,col3_cnt FROM mytable a JOIN (SELECT @n3 := 0) m3 WHERE col3_cnt IS NOT NULL) c 
ON a.s_no=c.s_no 
where c.col3_cnt is not null 
ORDER BY a.s_no;

answered Oct 9, 2015 at 5:50

Zafar Malik

6,8723 gold badges23 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3998848 Over a year ago

Impressive Zafar, I was able to rerun your code from sqlfiddle and it worked! I'm currently getting an error: "Error Code: 1137. Can't reopen table: 'a'" when I modify it w/ to match my db and table though. Thoughts? Is it possible it's because my sp creates a temporary table?

user3998848 Over a year ago

And the temporary table is exactly the issue. Thanks again. dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html

Collectives™ on Stack Overflow

MYSQL removing nulls from each column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related