MySQL Using merging two identical tables and use GROUP BY-CodePudding

I want to select from two identical tables using UNION ALL and GROUP BY. However, the Group BY doesn't work. Here is my query:

SELECT type , COUNT(subscription.id) as number ,SUM(subscription.amount) as total 
FROM subscription 
WHERE DATE(subscription.timestamp) BETWEEN '2022-10-18' AND '2022-10-18'
UNION ALL
SELECT type , COUNT(archive_subscription.id) as number ,SUM(archive_subscription.amount) as total 
FROM archive_subscription   
WHERE DATE(archive_subscription.timestamp) BETWEEN '2022-10-18' AND '2022-10-18'
GROUP BY type

The result is like the following:

type	number	amount
1	2	180000
1	1	80000

What I want to do is two merge both table using GROUP BY but it won't work:

type	number	amount
1	3	260000

Please, any suggestions? Thanks

CodePudding user response：

The first subquery in your union is missing its GROUP BY clause. Instead, try taking a union first and then aggregate:

SELECT type, COUNT(*) AS number, SUM(amount) AS total
FROM
(
    SELECT type, amount
    FROM subscription
    WHERE timestamp BETWEEN '2022-10-18' AND '2022-10-18'
    UNION ALL
    SELECT type, amount
    FROM archive_subscription
    WHERE timestamp BETWEEN '2022-10-18' AND '2022-10-18'
) t
GROUP BY type;

Note that the ranges in your WHERE clauses are trivial. If you really want to restrict to a single date, just use WHERE timestamp = '2022-10-18' instead.