MySQL transpose columns in rows for a query result

Question

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

Tomtom · Accepted Answer · 2022-10-26 12:46:38Z

2

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

edited Oct 26, 2022 at 12:46

answered Oct 26, 2022 at 11:34

Tomtom

884 bronze badges

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

1 Comment

Lilly_Co Over a year ago

Thanks ! It works perfectly :) So now I'm curious how to do for the 2nd option

Delta · Accepted Answer · 2022-10-26 11:34:00Z

0

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

answered Oct 26, 2022 at 11:34

Delta

5672 silver badges16 bronze badges

2 Comments

Lilly_Co Over a year ago

The code returns an error is because the Alias used. I'll go with @Tomtom answer how is quite the same idea than yours.

Lilly_Co Over a year ago

For the second option, do you know how I could do to have the result in 2 columns as Null and Not_null ?

Collectives™ on Stack Overflow

MySQL transpose columns in rows for a query result

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related