Нужно избегать умножения sum() в соединении

Мои запросы mysql...

SELECT row_number() over (order by p.invoice_number) as 'S.No.'
     , p.created_at as 'invoice_date'
     , p.vendor_name
     , p.invoice_number
     , format(sum(p.cost),0,'en_IN') as total_cost
     , v.accountno as accountno
     , v.paid_date as paid_date
     , format(sum(v.paid_amount),0,'en_IN') as paid_amount 
  from purchases p
  join vendor_accounts v
    on p.invoice_number = b.invoice_number 
 group 
    by p.invoice_number 
 having p.vendor_name = 'govardhan';
+-------+---------------------+-------------+----------------+------------+-----------+---------------------+-------------+
| S.No. | invoice_date        | vendor_name | invoice_number | total_cost | accountno | paid_date           | paid_amount |
+-------+---------------------+-------------+----------------+------------+-----------+---------------------+-------------+
|     1 | 2020-12-12 20:12:33 | govardhan   | 061067         | 79,980     | JBL       | 2020-12-12 20:38:30 | 2,50,000    |
+-------+---------------------+-------------+----------------+------------+-----------+---------------------+-------------+

Выходные данные умножаются на число vendor_name (10 раз) и другую таблицу vendor_name (2 раза), всего 20

мой вывод должен быть таким

+-------+---------------------+-------------+----------------+------------+-----------+---------------------+-------------+
| S.No. | invoice_date        | vendor_name | invoice_number | total_cost | accountno | paid_date           | paid_amount |
+-------+---------------------+-------------+----------------+------------+-----------+---------------------+-------------+
|     1 | 2020-12-12 20:12:33 | govardhan   | 061067         | 39,990     | JBL       | 2020-12-12 20:38:30 | 2,50,00    |
+-------+---------------------+-------------+----------------+------------+-----------+---------------------+-------------+

Пожалуйста, помогите мне получить резолюции. Заранее спасибо.

Образец данных:

mysql> select * from purchases where vendor_name='gowri';

+------+-------------+----------+-------+---------------+----------------+---------------+----------+-------------------+-------------+-------------------+----------------+---------------------+------------+
| id   | vendor_name | location | cost  | cost_of_price | invoice_number | serial_number | paid_GST | profit_percentage | sales_price | tag               | barcode        | created_at          | updated_at |
+------+-------------+----------+-------+---------------+----------------+---------------+----------+-------------------+-------------+-------------------+----------------+---------------------+------------+
| 1654 | gowri       | KPM      | 2999  | 3149          | 3614           | 01            | 5        |                35 |        4251 | gowri/314/3614/01 | 16542012122028 | 2020-12-12 20:28:53 | NULL       |
| 1655 | gowri       | KPM      | 2999  | 3149          | 3614           | 01            | 5        |                35 |        4251 | gowri/314/3614/01 | 16552012122028 | 2020-12-12 20:28:54 | NULL       |
| 1656 | gowri       | KPM      | 2999  | 3149          | 3614           | 01            | 5        |                35 |        4251 | gowri/314/3614/01 | 16562012122028 | 2020-12-12 20:28:54 | NULL       |
| 1657 | gowri       | KPM      | 2999  | 3149          | 3614           | 01            | 5        |                35 |        4251 | gowri/314/3614/01 | 16572012122028 | 2020-12-12 20:28:54 | NULL       |
| 1658 | gowri       | KPM      | 2999  | 3149          | 3614           | 01            | 5        |                35 |        4251 | gowri/314/3614/01 | 16582012122028 | 2020-12-12 20:28:54 | NULL       |
| 1659 | gowri       | KPM      | 2999  | 3149          | 3614           | 01            | 5        |                35 |        4251 | gowri/314/3614/01 | 16592012122028 | 2020-12-12 20:28:54 | NULL       |
| 1660 | gowri       | KPM      | 2999  | 3149          | 3614           | 01            | 5        |                35 |        4251 | gowri/314/3614/01 | 16602012122028 | 2020-12-12 20:28:55 | NULL       |
| 1661 | gowri       | KPM      | 2999  | 3149          | 3614           | 01            | 5        |                35 |        4251 | gowri/314/3614/01 | 16612012122028 | 2020-12-12 20:28:55 | NULL       |
| 1662 | gowri       | KPM      | 2999  | 3149          | 3614           | 01            | 5        |                35 |        4251 | gowri/314/3614/01 | 16622012122028 | 2020-12-12 20:28:55 | NULL       |
| 1663 | gowri       | KPM      | 2999  | 3149          | 3614           | 01            | 5        |                35 |        4251 | gowri/314/3614/01 | 16632012122028 | 2020-12-12 20:28:55 | NULL       |
| 1664 | gowri       | KPM      | 3999  | 4199          | 3614           | 02            | 5        |                35 |        5669 | gowri/419/3614/02 | 16642012122030 | 2020-12-12 20:30:21 | NULL       |
| 1665 | gowri       | KPM      | 3999  | 4199          | 3614           | 02            | 5        |                35 |        5669 | gowri/419/3614/02 | 16652012122030 | 2020-12-12 20:30:21 | NULL       |
| 1666 | gowri       | KPM      | 3999  | 4199          | 3614           | 02            | 5        |                35 |        5669 | gowri/419/3614/02 | 16662012122030 | 2020-12-12 20:30:22 | NULL       |
| 1667 | gowri       | KPM      | 3999  | 4199          | 3614           | 02            | 5        |                35 |        5669 | gowri/419/3614/02 | 16672012122030 | 2020-12-12 20:30:22 | NULL       |
| 1668 | gowri       | KPM      | 3999  | 4199          | 3614           | 02            | 5        |                35 |        5669 | gowri/419/3614/02 | 16682012122030 | 2020-12-12 20:30:22 | NULL       |
| 1669 | gowri       | KPM      | 3999  | 4199          | 3614           | 02            | 5        |                35 |        5669 | gowri/419/3614/02 | 16692012122030 | 2020-12-12 20:30:22 | NULL       |
| 1670 | gowri       | KPM      | 3999  | 4199          | 3614           | 02            | 5        |                35 |        5669 | gowri/419/3614/02 | 16702012122030 | 2020-12-12 20:30:23 | NULL       |
| 1671 | gowri       | KPM      | 3999  | 4199          | 3614           | 02            | 5        |                35 |        5669 | gowri/419/3614/02 | 16712012122030 | 2020-12-12 20:30:23 | NULL       |
| 1672 | gowri       | KPM      | 3999  | 4199          | 3614           | 02            | 5        |                35 |        5669 | gowri/419/3614/02 | 16722012122030 | 2020-12-12 20:30:24 | NULL       |
| 1673 | gowri       | KPM      | 3999  | 4199          | 3614           | 02            | 5        |                35 |        5669 | gowri/419/3614/02 | 16732012122030 | 2020-12-12 20:30:24 | NULL       |
| 1694 | gowri       | JBL      | 46666 | 48999         | 3615           | 01            | 5        |                35 |       66149 | gowri/489/3615/01 | 16942012130215 | 2020-12-13 02:15:55 | NULL       |
| 1695 | gowri       | JBL      | 46666 | 48999         | 3615           | 01            | 5        |                35 |       66149 | gowri/489/3615/01 | 16952012130215 | 2020-12-13 02:15:55 | NULL       |
| 1696 | gowri       | JBL      | 46666 | 48999         | 3615           | 01            | 5        |                35 |       66149 | gowri/489/3615/01 | 16962012130215 | 2020-12-13 02:15:55 | NULL       |
| 1697 | gowri       | JBL      | 46666 | 48999         | 3615           | 01            | 5        |                35 |       66149 | gowri/489/3615/01 | 16972012130215 | 2020-12-13 02:15:56 | NULL       |
| 1698 | gowri       | JBL      | 46666 | 48999         | 3615           | 01            | 5        |                35 |       66149 | gowri/489/3615/01 | 16982012130215 | 2020-12-13 02:15:56 | NULL       |
| 1699 | gowri       | JBL      | 46666 | 48999         | 3615           | 01            | 5        |                35 |       66149 | gowri/489/3615/01 | 16992012130215 | 2020-12-13 02:15:56 | NULL       |
| 1700 | gowri       | JBL      | 46666 | 48999         | 3615           | 01            | 5        |                35 |       66149 | gowri/489/3615/01 | 17002012130215 | 2020-12-13 02:15:56 | NULL       |
| 1701 | gowri       | JBL      | 46666 | 48999         | 3615           | 01            | 5        |                35 |       66149 | gowri/489/3615/01 | 17012012130215 | 2020-12-13 02:15:56 | NULL       |
| 1702 | gowri       | JBL      | 46666 | 48999         | 3615           | 01            | 5        |                35 |       66149 | gowri/489/3615/01 | 17022012130215 | 2020-12-13 02:15:56 | NULL       |
| 1703 | gowri       | JBL      | 46666 | 48999         | 3615           | 01            | 5        |                35 |       66149 | gowri/489/3615/01 | 17032012130215 | 2020-12-13 02:15:56 | NULL       |
| 1704 | gowri       | JBL      | 46666 | 48999         | 3615           | 01            | 5        |                35 |       66149 | gowri/489/3615/01 | 17042012130215 | 2020-12-13 02:15:56 | NULL       |
| 1705 | gowri       | JBL      | 46666 | 48999         | 3615           | 01            | 5        |                35 |       66149 | gowri/489/3615/01 | 17052012130215 | 2020-12-13 02:15:56 | NULL       |
| 1706 | gowri       | JBL      | 46666 | 48999         | 3615           | 01            | 5        |                35 |       66149 | gowri/489/3615/01 | 17062012130215 | 2020-12-13 02:15:56 | NULL       |
| 1707 | gowri       | JBL      | 46666 | 48999         | 3615           | 01            | 5        |                35 |       66149 | gowri/489/3615/01 | 17072012130215 | 2020-12-13 02:15:56 | NULL       |
| 1708 | gowri       | JBL      | 46666 | 48999         | 3615           | 01            | 5        |                35 |       66149 | gowri/489/3615/01 | 17082012130215 | 2020-12-13 02:15:56 | NULL       |
+------+-------------+----------+-------+---------------+----------------+---------------+----------+-------------------+-------------+-------------------+----------------+---------------------+------------+
35 rows in set (0.00 sec)

mysql> select * from vendor_accounts where vendor_name='gowri';

+----+-------------+----------------+-----------+-------------+---------------------+
| id | vendor_name | invoice_number | accountno | paid_amount | paid_date           |
+----+-------------+----------------+-----------+-------------+---------------------+
|  2 | gowri       | 3614           | NULL      | NULL        | 2020-12-12 20:28:55 |
|  3 | gowri       | 3614           | NULL      | NULL        | 2020-12-12 20:30:25 |
| 10 | gowri       | 3614           | XXX1035   | 25000       | 2020-12-13 02:13:52 |
| 11 | gowri       | 3615           | NULL      | NULL        | 2020-12-13 02:15:57 |
+----+-------------+----------------+-----------+-------------+---------------------+
4 rows in set (0.00 sec)

Фактический вывод должен выглядеть так

+-------+---------------------+-------------+----------------+------------+---------------------+-------------+
| S.No. | invoice_date        | vendor_name | invoice_number | total_cost | paid_date           | paid_amount |
+-------+---------------------+-------------+----------------+------------+---------------------+-------------+
| 1     | 2020-12-12 20:28:53 | gowri       | 3614           | 69,980      | 2020-12-12 20:28:55 | 5,00,000    |
| 2     | 2020-12-13 02:15:55 | gowri       | 3615           | 6,99,990   | 2020-12-13 02:15:57 | NULL        |
|       |                     |             | TOTAL          | 7,69,970   |                     | 25,000      |
+-------+---------------------+-------------+----------------+------------+---------------------+-------------+

Предоставьте образцы данных и желаемые результаты в виде табличного текста. Ваш запрос не похож на допустимый запрос агрегации.

— 12.12.2020 20:28

Объединяйте по purchases, затем присоединяйтесь к vendor_accounts.

— 12.12.2020 21:34

@GMB - сэр, пожалуйста, найдите результат отредактированных запросов и примеры данных в ответе ниже

— 12.12.2020 22:35

@GowthamKrisher: это должно было быть опубликовано как изменение вашего вопроса, а не как ответ. Я скопировал соответствующую информацию на ваш вопрос - вам следует рассмотреть возможность удаления вашего ответа ниже.

— 12.12.2020 23:14

@GMB да, сэр, и ваш код у меня хорошо работает, спасибо за большую помощь

— 12.12.2020 23:26

mysql sql sum inner-join aggregate-functions

12.12.2020 20:20

Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для разработчиков

В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...

Как построить CRUD-приложение в Laravel

Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...

Освоение PHP и управление базами данных: Создание собственной СУБД - часть II

В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...

Документирование API с помощью Swagger на Springboot

В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .

Роли и разрешения пользователей без пакета Laravel 9

Этот пост изначально был опубликован на techsolutionstuff.com .

Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM

В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.

102

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

В обеих таблицах есть несколько строк для данного кортежа поставщика/счета, поэтому объединение умножает строку, и вы получаете неправильные результаты в агрегатных функциях.

Сначала вам нужно будет предварительно агрегировать подзапросы, а затем присоединиться:

select 
    row_number() over (order by p.invoice_number) as rn,
    p.*,
    va.total_paid_amount,
    va.max_paid_date
from (
    select vendor_name, invoice_number, 
        sum(p.cost) as total_cost, 
        max(created_at) as max_created_at
    from purchases 
    group by vendor_name, invoice_number
) p
inner join (
    select vendor_name, invoice_number, 
        sum(paid_amount) as total_paid_amount, 
        max(paid_date) as max_paid_date
    from vendor_accounts
    group by vendor_name, invoice_number
) va on va.invoice_number = p.invoice_number and va.vendor_name = p.vendor_name

Обратите внимание, что я использовал агрегатные функции для столбцов даты; в отличие от вашего исходного запроса. Эти столбцы не являются частью предложения group by, поэтому нам нужно их агрегировать.

Это приносит результаты для всех поставщиков сразу. Вы можете легко изменить запрос для фильтрации по заданному имени поставщика, добавив предложение where в конце запроса.

сэр, мы можем подключиться к Anydesk :)

— 12.12.2020 23:06

12.12.2020 22:58