"union" to avoid duplicates

Question

https://dbfiddle.uk/zspUa8WK

I need to export name and sum the invoice of my customer since 2022. So I need to get only customers which bought since 2 years and the sum.

No problem for that:

select i.customer_id, sum(amount)
from invoices i
inner join customer c2 on c2.customer_id = i.customer_id 
where i.invoice_date > '01/12/2022'
group by i.customer_id , c2.customer_id 

id	name	sum
2	Johnson	300
3	Ali	100

I need, in the same export, to get customer modified this year

I use this query

select c.customer_id, c.name,  '' from customer c 
where c.modificationDate > '01/12/2024'
group by c.customer_id , c.name

id	name
1	Smith
3	Ali

Because I need this in the same export, I use a UNION

select i.customer_id, c2.name , sum(amount) sum from invoices i
   inner join customer c2 on c2.customer_id =i.customer_id 
   where i.invoice_date > '01/12/2022'
   group by i.customer_id , c2.customer_id , c2.name 
union  
select c.customer_id, c.name,  '' from customer c 
where c.modificationDate > '01/12/2024'
group by c.customer_id , c.name 

id	name	sum
1	Smith	0.0000
2	Johnson	300.0000
3	Ali	0.0000
3	Ali	100.0000

My problem is that I would like that mister ALI be only once in my result, ie

id	name	sum
1	Smith	0.0000
2	Johnson	300.0000
3	Ali	100.0000

How could I do that?

DDL script for testing

CREATE TABLE customer (
    customer_id int NOT NULL,
    name varchar(100) COLLATE French_CI_AS NULL,
    city varchar(100) COLLATE French_CI_AS NULL,
    modificationDate date NULL
);
INSERT INTO customer
(customer_id, name, city, modificationDate)
VALUES(1, N'Smith', N'NY', '2024-12-03 00:00:00.000');
INSERT INTO customer
(customer_id, name, city, modificationDate)
VALUES(2, N'Johnson', N'London', '2015-01-01 00:00:00.000');
INSERT INTO customer
(customer_id, name, city, modificationDate)
VALUES(3, N'Ali', N'Bombay', '2024-12-05 00:00:00.000');

CREATE TABLE invoices (
    invoice_id int NOT NULL,
    customer_id int NULL,
    amount money NULL,
    invoice_date datetime NULL,
    CONSTRAINT invoices_pk PRIMARY KEY (invoice_id)
);
INSERT INTO invoices
(invoice_id, customer_id, amount, invoice_date)
VALUES(1, 1, 100.0000, '2018-01-11 18:06:00.333');
INSERT INTO invoices
(invoice_id, customer_id, amount, invoice_date)
VALUES(2, 2, 200.0000, '2023-09-03 18:06:26.620');
INSERT INTO invoices
(invoice_id, customer_id, amount, invoice_date)
VALUES(3, 2, 100, '2023-06-14 18:07:17.350');
INSERT INTO invoices
(invoice_id, customer_id, amount, invoice_date)
VALUES(4, 3, 100, '2024-11-20 18:08:10.510');

Answer 1

A UNION is not required. You can use 1 SELECT with a SUM of Amount when i.invoice_date > '01/12/2022' and SUM 0 otherwise. In this solution the query must start with the customer table and the Invoices table will need to be add via a LEFT JOIN.

SELECT c.customer_id, c.name, SUM(CASE WHEN i.invoice_date > '01/12/2022' THEN i.amount ELSE 0 END) as Sum
FROM customer c
LEFT JOIN invoices i ON c.customer_id =i.customer_id 
WHERE i.invoice_date > '01/12/2022' OR c.modificationDate > '01/12/2024'
GROUP BY c.customer_id, c.name
ORDER BY c.Customer_ID

fiddle

Answer 2

Perform the union first then apply the GROUP BY. For the modified records expose 0 as the amount.

SELECT t.customer_id , t.name, sum(t.amount) AS sum 
FROM
(  
    --customers invoiced since 01/12/2022 
    SELECT i.customer_id, c2.name, i.amount
    FROM invoices i
    INNER JOIN customer c2 ON c2.customer_id =i.customer_id 
    WHERE i.invoice_date > '01/12/2022'

    UNION

    --customers modified since 01/12/2024
    SELECT c.customer_id, c.name,  0 AS amount 
    FROM  customer c 
    WHERE c.modificationDate > '01/12/2024'
) t 
GROUP BY t.customer_id , t.name
ORDER BY t.Customer_ID

fiddle

customer_id	name	sum
1	Smith	0.0000
2	Johnson	300.0000
3	Ali	100.0000