Как объединить две таблицы и суммировать значения с одинаковым идентификатором в SQL?

ProgramBox

Как объединить две таблицы и суммировать значения с одинаковым идентификатором в SQL?

Post author:admin
Запись опубликована:4 ноября, 2022
Post category:Вопросы по программированию

#sql #sqlite

Вопрос:

Я новичок в SQL, извините, если вопрос окажется тривиальным.

У меня есть две таблицы:

Таблица 1 «Клиенты»:

  CREATE TABLE customer (ID, Name, Surname);
 INSERT INTO customer (ID, Name, Surname)
 VALUES (1, 'Smith', 'Hulk'),
 (2, 'Jake', 'Brook'),
 (3, 'Vladimir', 'Computin'),
 (4, 'Joe', 'Door');

Таблица 2 «Платежи»:

  CREATE TABLE payment (payment_id, customer_id, payment_date, payment_amount);
 INSERT INTO payment (payment_id, customer_id, payment_date, payment_amount)
 VALUES (1, 1, "11/09/2011", 100),
 (2, 2, "15/10/2013", 50),
 (3, 4, "2/1/2011", 30),
 (4, 3, "12/09/2011", 200),
 (5, 2, "15/10/2013", 200),
 (6, 3, "2/1/2011", 10),
 (7, 1, "11/09/2011", 120),
 (8, 4, "15/10/2013", 100);

Желаемый результат:

 CREATE TABLE payment (Name, Surname, total_payments);
 INSERT INTO payment (Name, Surname, total_payments)
 VALUES ('Smith', 'Hulk',220),
 ('Jake', 'Brook', 250),
 ('Vladimir', 'Computin', 210),
 ('Joe', 'Door', 130)

Вот что я пробовал:

 SELECT payments.ID, sum(payments.payment_amount)
FROM payments
GROUP BY payments.ID

Это суммирует записи в таблице «платежи» с одинаковым идентификатором.

Однако я не знаю, как использовать идентификатор, чтобы имя и фамилия указывались перед total_payments (как в желаемом выводе).

Любая помощь будет оценена!

1. Сначала вам нужно объединить две таблицы вместе. Вам нужно найти «связь» между вашими таблицами, чтобы вы могли объединять платежи вместе с клиентом, который произвел этот платеж. Прежде чем начать применять предложения Group By к вашему запросу, сначала напишите объединения, чтобы связать ваши данные. В этом случае вы бы объединили свои таблицы с помощью Customer_ID

Ответ №1:

Вам может понадобиться JOIN ввести их в запрос, а затем выполнить соответствующую агрегацию:

 SELECT c.ID, c.Name, c.SurName, sum(p.payment_amount)
FROM clients c LEFT JOIN
     payments p
     ON c.id = p.customer_id
GROUP BY c.ID, c.Name, c.SurName;

Примечание: использование коррелированного подзапроса часто выполняется быстрее:

 SELECT c.*, 
       (SELECT SUM(p.payment_amount)
        FROM payments p
        WHERE c.id = p.customer_id
       ) as total_payments
FROM clients c ;

В частности, это может эффективно использовать индекс on payments(customer_id, payment_amount) .

Ответ №2:

Вам нужно LEFT объединение (на всякий случай, если у клиента нет никаких платежей) customer to payment и aggregation:

 SELECT c.Name, c.Surname, 
       COALESCE(SUM(p.payment_amount), 0) payment_amount
FROM customer c LEFT JOIN payment p
ON p.customer_id = c.id
GROUP BY c.ID, c.Name, c.Surname

Смотрите демонстрацию.
Результаты:

 > Name     | Surname  | payment_amount
> :------- | :------- | -------------:
> Smith    | Hulk     |            220
> Jake     | Brook    |            250
> Vladimir | Computin |            210
> Joe      | Door     |            130

1. Спасибо за ваш ответ, он отлично работает! и спасибо, что сообщили мне о dbfiddle.uk !! отличный ресурс! Жаль, что я могу принять только один ответ.

2. @Geom есть причина, по которой вы приняли последний ответ?

3. нет технической причины. Это просто бритва Оккама. Почему-то всегда привлекает самое простое решение. К сожалению, я еще не знаком с «COALESCE», поэтому я лучше понял, что происходит в другом решении. Хотелось бы, чтобы был способ принять оба ответа, поскольку оба мне очень помогли!

4. @Geom … не знаком с «COALESCE» … тогда вам следует его поискать. Вам нужно объединить () на случай, если у клиента нет никаких платежей, чтобы вы получили 0 в качестве результата, а не NULL.

5. Я сделаю, спасибо!

Ответ №3:

Попробуйте :

 select c.Name, c.Surname, SUM(p.payment_amount)
from Payments as p, Clients as c
where p.customerID = c.ID
group by c.Name, c.Surname

Я думаю, это сработает