why UNION ALL command in mysql doesn't give back any results?

user_15996926 0
Viewed 58

I am trying to merge two queries into one, but UNION is not working for me. Here is the code:

SELECT
Customer_A,
Activity,
Customer_P,
Purchase
FROM (
SELECT 
buyer_id as Customer_A,
COUNT(buyer_id) As Activity
FROM 
customer_info_mxs
GROUP BY buyer_id
UNION ALL
SELECT
buyer_id as Customer_P,
SUM(purchase_amount) As Purchase
FROM 
customer_info_mxs
GROUP BY buyer_id
)sub

I expect to have 4 columns as a result, but I get 2 instead (Customer_A) and(Activity).

mysql
3 Answers

If the query is supposed to return a list of customers, their number of purchases, and the total amount they’ve spent, then you can use a single query like this:

SELECT mxs.buyer_id as Customer,
       COUNT(mxs.purchase_id) As Activity,
       SUM(mxs.purchase_amount) As Purchases
  FROM customer_info_mxs mxs
 GROUP BY mxs.buyer_id;

Otherwise, your first subquery will always be a buyer_id and a value of 1.

Be sure to change purchase_id to whatever the unique id is for each purchase if you wish to see that number.

user_149528320

I think there is some confusion about the union statement. The union statement returns a row set that is the sum of all of the 'unioned' queries; since these queries have only 2 columns, the combined output only has two columns. The fact that the columns have different names is irrelevant. The column names in the output are being applied from the first query of the union.

One option is to just do

select buyer_id, count(buyer_id), sum(purchase_amount) from customer_info_mxs group by buyer_id

From your question, it looks like you are trying to do a pivot, turning some of the rows into additional columns. That could be done with ... some difficulty.

user_84133150

i read your comment, 'main goal is to creat a dataset in which returns 5 columns as: Customer_A, Activity (top 100), customer_P, Purchase(top 100), inner join of activity and purchase'

please try this query

SET @row_number = 0, @row_number2 = 0; 
SELECT t1.Customer_A,t1.Activity, t2.Customer_P, t2.Purchase
from (
      SELECT (@row_number:=@row_number + 1) AS n, t.Customer_a, t.Activity
      from (
            select buyer_id as Customer_A,COUNT(buyer_id) As Activity
            FROM customer_info_mxs
            GROUP BY buyer_id
            order by Activity desc
            Limit 100
            )t
      ) t1
left join (
      SELECT (@row_number2:=@row_number2 + 1) AS n, 
      FROM (
            select buyer_id as Customer_P, SUM(purchase_amount) Purchase
            FROM customer_info_mxs
            GROUP BY buyer_id
            order by Purchase desc
            Limit 100
            )t
      ) t2 on t2.n=t1.n

basic idea is, i just create some temporary number 0-99 to table 1 (t1) and join to temporary number on table 2 (t2)

user_50267090
Related