While looping how to get sum of the fields..?

General PHP Help
#1

Dear All,

this is my table1-booking

bid booking_id   product_name  quantity  amount
1    100                shirt                    10            1500
2    101                tie                       10            3200
3    102                pant                    10            2900
4    102                pant                    10            2900

table2-payment

pid   booking_id  paid_amt
1        100              450
2        102              250
3        100              260
4        100              140

How to show sum of paid amount in while loop while fetching the records…
i tried sum(payment.paid_amt) but i while loop it will calculate all paid amount sum not booking_id wise
kindly help me…

thanks,

#2

Just join the tables and group by booking_id. This will give you only one row for each booking, and you can add SUM(payment.paid_amt) to get the paid total in each

https://www.db-fiddle.com/f/n1AVs5Ye36KHbmcTWyEyrY/0

#3

this is my table1-booking

bid    booking_id     product_name   quantity amount
1       100                   shirt                    10            1500
2       101                    tie                      10             3200
3       102                     pant                   10            2900
4       102                    shirt                     10           2900

this is my case in that one booking id user can book multiple items…
so if i am doing like it will multiply paid amount by number of records…

#4

I’m not sure I understand, what do you expect the paid_totals to be?

This is the output I get from the db-fiddle:

booking_id product_name quantity amount paid_total
100 shirt 10 1500 850
101 tie 10 3200 null
102 pant 10 2900 250

edit: I see of your last post that you have changed your booking table. In order to properly answer this you should add your actual db structure and what data you wish to end up with (preferably with example data)

#5

Here booking id not unique…
so that loop will load two times and sum of paid amount will be multiplied by 2 (number of records present in the booking table)
i tried your method… it showing like that

thanks,

#6

In order to properly answer this you should add your actual db structure and what data you wish to end up with (preferably with example data)

#7

To do specifically what you are asking -

You would JOIN the table that holds the main booking information, where the booking_id is defined, with the payment table, grouped by the booking_id. The 1st table you have shown is the booking details, the items that are part of the booking, and it doesn’t directly have anything to do with getting the sum of the paid amounts.

However, I suspect your overall goal is to get both the amount booked/owed and the amount paid and/or sum/subtract those to show the amount due. If so, you need to use a UNION ALL query, between two SELECT queries, one to get the sum from the booking details table, grouped by the booking_id, and the second to get the sum from the payment table, grouped by the booking_id. If you want to perform the sum/subtraction of those two values in the query, you would use the UNION ALL query as a sub-query. The outer query would sum the sub-totals (the amount paid would need to made negative) and group by the booking_id.

Sponsor our Newsletter | Privacy Policy | Terms of Service