UNION query issue in MySQL

Question

Look at my UNION query:

SELECT customer_id
     , name as cname
     , CASE
        WHEN address <> ''
          THEN CONCAT(address, ', ', city)
        ELSE city
       END as address
     , null as total_sale
     , null as total_paid
     , null as total_due
FROM customer c

UNION 

SELECT customer_id
     , null as cname
     , null as address
     , sum(amount_due_tot) as total_sale
     , sum(amount_paid_tot) as total_paid
     , sum(amount_due_balance_tot) as total_due
FROM (
  SELECT bad.basket_id as baskets
       , bad.customer_id as customer_id
       , bad.amount_due as amount_due_tot
       , sum(COALESCE(p.amount_paid, 0)) as amount_paid_tot
       , bad.amount_due - SUM(COALESCE(p.amount_paid, 0)) as amount_due_balance_tot
       , sale_status_id
  FROM basket_amount_due bad
    LEFT JOIN basket b USING (basket_id)
    LEFT JOIN basket_payment p USING (basket_id)
    LEFT JOIN customer c ON b.customer_id = c.customer_id
  WHERE b.sale_status_id = 4
  GROUP BY basket_id, sale_status_id
) d
GROUP BY customer_id;

It returns a result as shown here:

+-------------+--------------------+-----------------+------------+------------+-----------+
| customer_id | cname              | address         | total_sale | total_paid | total_due |
+-------------+--------------------+-----------------+------------+------------+-----------+
|           1 | Customer Name 01   | NULL            |       NULL |       NULL |      NULL |
|           2 | Customer Name 02   | Address 02      |       NULL |       NULL |      NULL |
|           3 | Customer Name 03   | Address 03      |       NULL |       NULL |      NULL |
|           4 | Customer Name 04   | Address 04      |       NULL |       NULL |      NULL |
|           5 | Customer Name 05   | Address 05      |       NULL |       NULL |      NULL |
|           6 | Customer Name 06   |                 |       NULL |       NULL |      NULL |
|           7 | Customer Name 07   |                 |       NULL |       NULL |      NULL |
|           1 | NULL               | NULL            |     927.08 |     927.08 |      0.00 |
|           3 | NULL               | NULL            |    2576.80 |    2376.80 |    200.00 |
|           4 | NULL               | NULL            |   12118.00 |    4210.25 |   7907.75 |
|           6 | NULL               | NULL            |     870.00 |     270.00 |    600.00 |
+-------------+--------------------+-----------------+------------+------------+-----------+

But from this UNION query I expect to get this result:

+-------------+--------------------+-----------------+------------+------------+-----------+
| customer_id | cname              | address         | total_sale | total_paid | total_due |
+-------------+--------------------+-----------------+------------+------------+-----------+
|           1 | Customer Name 01   | NULL            |     927.08 |     927.08 |      0.00 |
|           2 | Customer Name 02   | Address 02      |       NULL |       NULL |      NULL |
|           3 | Customer Name 03   | Address 03      |    2576.80 |    2376.80 |    200.00 |
|           4 | Customer Name 04   | Address 04      |   12118.00 |    4210.25 |   7907.75 |
|           5 | Customer Name 05   | Address 05      |     870.00 |     270.00 |    600.00 |
|           6 | Customer Name 06   |                 |       NULL |       NULL |      NULL |
|           7 | Customer Name 07   |                 |       NULL |       NULL |      NULL |
+-------------+--------------------+-----------------+------------+------------+-----------+

Can anybody tell me what is wrong I have made in this query?

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2021-09-28 10:21:10Z

3

Instead of union you need to use join.

SELECT c.customer_id
     , c.name as cname
     , CASE
        WHEN address <> ''
          THEN CONCAT(c,address, ', ', c.city)
        ELSE city
       END as address
     , sum(d.amount_due_tot) as total_sale
     , sum(d.amount_paid_tot) as total_paid
     , sum(d.amount_due_balance_tot) as total_due
FROM customer c
Left join
(
  SELECT bad.basket_id as baskets
       , bad.customer_id as customer_id
       , bad.amount_due as amount_due_tot
       , sum(COALESCE(p.amount_paid, 0)) as amount_paid_tot
       , bad.amount_due - SUM(COALESCE(p.amount_paid, 0)) as amount_due_balance_tot
       , sale_status_id
  FROM basket_amount_due bad
    LEFT JOIN basket b USING (basket_id)
    LEFT JOIN basket_payment p USING (basket_id)
    LEFT JOIN customer c ON b.customer_id = c.customer_id
  WHERE b.sale_status_id = 4
  GROUP BY basket_id, sale_status_id
) d
on c.customer_id = d.customer_id
GROUP BY c.customer_id;

edited Sep 28, 2021 at 10:21

answered Sep 28, 2021 at 9:50

Kazi Mohammad Ali Nur Romel

16.2k2 gold badges17 silver badges29 bronze badges

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

2 Comments

user3733831 Over a year ago

To get it to work GROUP BY customer_id needed to add at the end of the query....

Kazi Mohammad Ali Nur Romel Over a year ago

@user3733831 sorry for the mistake. Best wishes.

Thorsten Kettner · Accepted Answer · 2021-09-28 10:42:44Z

The second part of your query selects all baskets with a due amount along with the customers. You want one result row per customer. So why do you group by basket and sale status? And why do you null the address? This doesn't seem to make much sense. I'd expect a mere:

SELECT c.customer_id
     , c.name AS cname
     , CONCAT_WS(', ', NULLIF(c.address, ''), c.city) AS address
     , SUM(p.amount_due) AS total_sale
     , COALESCE(SUM(p.amount_paid), 0) AS total_paid
     , SUM(p.amount_due) - COALESCE(SUM(p.amount_paid), 0) AS total_due
FROM basket_amount_due bad
JOIN basket b USING (basket_id)
LEFT JOIN basket_payment p USING (basket_id)
JOIN customer c ON c.customer_id = bad.customer_id
WHERE b.sale_status_id = 4
GROUP BY c.customer_id
ORDER BY c.customer_id;

This only shows customers that have an entry in the basket_amount_due table. If you want all customers, then select from customer and outer join the other tables.

This query also assumes that there is at most one row in basket_amount_due per basket. So does yours. If this isn't the case, then don't join its rows with the basket_payment rows, but aggregate before joining. Else you'd get incorrect results.

Collectives™ on Stack Overflow

UNION query issue in MySQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related