MySQL transpose columns in rows for a query result-CodePudding

In my table Product in MySQL I would like to count how many Product_Id has been Saved in Fav, Purchased or Delivered. Then organize the result in rows with null or not null by Fav, Purchase, Delivery in column or the inverse, like I explain below.

With this query:

SELECT COUNT(IF(Fav IS NOT NULL, ID, NULL)) AS "Fav_Not-null", COUNT(IF(Fav IS NULL, ID, NULL)) AS "Fav_Null" ,
COUNT(IF(Purchase IS NOT NULL, ID, NULL)) AS "Purchase_Not-null", COUNT(IF(Purchase IS NULL, ID, NULL)) AS "Purchase_Null" ,
COUNT(IF(Delivery IS NOT NULL, ID, NULL)) AS "Delivery_Not-null", COUNT(IF(Delivery IS NULL, ID, NULL)) AS "Delivery_Null"
FROM Product

I have this result:

#   Fav_Not-null    Fav_null    Purchase_Not-null   Purchase_null   Delivery_Not-null   Delivery_null
1   75              25          53                  47              27                  73

It's ok but I would like to show the result in different way like:

            Fav     Purchase    Delivery
Null        25      47          73
Not-null    75      53          27

or like:

            Null    Not-null 
Fav         25      75
Purchase    47      53
Delivery    73      27

Thanks for the help

CodePudding user response：

I couldn't run a test since I don't how your db is built, but this might do the work for your first option :

SELECT 'null' as '', COUNT(IF(Fav IS NULL, ID, NULL)) as "Fav", COUNT(IF(Purchase IS NULL, ID, NULL)) AS "Purchase", COUNT(IF(Delivery IS NULL, ID, NULL)) AS "Delivery"
FROM Product
union
SELECT 'not-null', COUNT(IF(Fav IS NOT NULL, ID, NULL)), COUNT(IF(Purchase IS NOT NULL, ID, NULL)), COUNT(IF(Delivery IS NOT NULL, ID, NULL))
FROM Product;

2nd option:

Select 'Fav' as '', COUNT(IF(Fav IS NULL, ID, NULL)) as 'Null', COUNT(IF(Fav IS NOT NULL, ID, NULL)) as 'Not-null'
FROM Product
UNION
Select 'Purchase', COUNT(IF(Purchase IS NULL, ID, NULL)), COUNT(IF(Purchase IS NOT NULL, ID, NULL))
FROM Product
UNION
select 'Delivery', COUNT(IF(Delivery IS NULL, ID, NULL)), COUNT(IF(Delivery IS NOT NULL, ID, NULL))
FROM Product

CodePudding user response：

Maybe you could use an UNION with two queries :

SELECT
    'Null' AS TYPE
    COUNT(IF(Fav IS NOT NULL, ID, NULL)) AS "Fav_Not-null" AS Fav,
    COUNT(IF(Purchase IS NULL, ID, NULL)) AS "Purchase_Null" AS Purchase,
    COUNT(IF(Delivery IS NULL, ID, NULL)) AS "Delivery_Null" AS Delivery
FROM Product

UNION

SELECT
    'Not-null' AS TYPE,
    COUNT(IF(Fav IS NULL, ID, NULL)) AS "Fav_Null" AS Fav,
    COUNT(IF(Purchase IS NOT NULL, ID, NULL)) AS "Purchase_Not-null" AS Purchase, 
    COUNT(IF(Delivery IS NOT NULL, ID, NULL)) AS "Delivery_Not-null" AS Delivery 
FROM Product