Two MYSQL statements with different columns names but from same table

Question

I have this two MySQL statements from same table :

SELECT
    `table1`.`product_id` As product_id,
    COUNT(DISTINCT(table1.user_id)) AS NonebuyersNumber
FROM table1
WHERE status = 1 AND `ispaid` != 2
GROUP BY `table1`.`product_id`

The second statement is :

SELECT l
    `table1`.`product_id` As product_id,
    COUNT(DISTINCT(table1.user_id)) AS BuyersNumber
FROM table1
WHERE `ispaid` = 1
GROUP BY `table1`.`product_id`

The result that I want is a table like this one :

I tried to use Union but doesn't work because I have two different columns

Any idea how I can get this 3rd table?

Tim Biegeleisen · Accepted Answer · 2017-12-16 07:28:54Z

1

Use conditional aggregation:

SELECT
    product_id,
    COUNT(DISTINCT CASE WHEN status = 1 AND ispaid != 2
                        THEN user_id ELSE NULL END) AS NonebuyersNumber
    COUNT(DISTINCT CASE WHEN ispaid = 1 THEN user_id ELSE NULL END) AS BuyersNumber
FROM table1
WHERE
    (status = 1 AND ispaid != 2) OR 
    ispaid = 1
GROUP BY
    product_id;

This should work because both of your queries aggregate over the product_id and the only differences are the WHERE clauses. We can combine the records from both queries and then use CASE expressions to target records intended for each original query.

edited Dec 16, 2017 at 7:28

answered Dec 16, 2017 at 7:22

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Comments

Pankaj Gadge · Accepted Answer · 2017-12-16 07:21:51Z

SELECT t1.product_id AS product_id
SELECT CASE WHEN t1.NonebuyersNumber IS NULL 
            THEN 0 
            ELSE t1.NonebuyersNumber 
            END 
    AS NonebuyersNumber,
SELECT CASE WHEN t2.BuyersNumber IS NULL 
            THEN 0 
            ELSE t2.BuyersNumber 
            END 
    AS BuyersNumber
FROM
(SELECT 
    `table1`.`product_id` As product_id , 
    COUNT(DISTINCT(table1.user_id)) AS NonebuyersNumber 
    FROM table1 WHERE status =1 
    AND `ispaid` != 2 
    GROUP BY `table1`.`product_id`) 
AS t1
INNER JOIN
(SELECT 
    `table1`.`product_id` As product_id , 
    COUNT(DISTINCT(table1.user_id)) AS BuyersNumber 
    FROM table1 WHERE `ispaid` = 1 
    GROUP BY `table1`.`product_id`) 
AS t2
ON t1.product_id = t2.product_id

Basically, you need following

Join both the views on product_id
Use CASE statements in select in case one of the buyers numbers is NULL

Collectives™ on Stack Overflow

Two MYSQL statements with different columns names but from same table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related