My mysql queries is...
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 |
+-------+---------------------+-------------+----------------+------------+-----------+---------------------+-------------+
Output takes multipliies count of vendor_name (10 times) & another table vendor_name (2 times) total 20
my output should be like
+-------+---------------------+-------------+----------------+------------+-----------+---------------------+-------------+
| 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 |
+-------+---------------------+-------------+----------------+------------+-----------+---------------------+-------------+
Please help me to get resolutions. Thanks in advance.
Sample data:
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)
Actual output should looks like
+-------+---------------------+-------------+----------------+------------+---------------------+-------------+
| 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 |
+-------+---------------------+-------------+----------------+------------+---------------------+-------------+
There are several rows in both tables for a given vendor/invoice tuple, so the join multiplies the row, and you get wroong results in the aggregate functions.
You would need to pre-aggregate in subqueries first, then join:
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
Note that I used aggregate functions on the date columns; unlike in your original query. These columns are not part of the group by clause, so we need to aggregate them.
This brings the results for all vendors at once. You can easily change the query to filter on a given vendor name by adding a where clause at the end of the query.