0

I'm making a query to calculate the summation of total and amount columns but with the calculation of raw-materials and growth profit, I get the raw-materials summation from the items table that has the price in it and calculate the growth profit by subtracting calculated raw-materials from the amount,

The problem comes when I run the query it gives me wrong values. like sum of amount

sum(`payment`.`amount`) as total amount

should be like 1425 but the result is 107100, I don't know why it does this in the image below I showed the results, that number 107100 it way more than it should be please I’m new someone help me

This is my query

 select `payment`.`date` as `Date`,
sum(`payment`.`total`) as `Total`,
sum(`payment`.`amount`) as `Paid amount`,
sum(items.price) as `R.M`,
 sum(`payment`.`amount`) - sum(items.price)   AS `G.P`,
`currennt_exp`.`expence` as `Current Expenses`,
 sum(`payment`.`amount`)-sum(items.price) - `currennt_exp`.`expence`  AS `Revenue`
 from `payment`,`paymen_information`,`bill_information`,`bills`,`items`,`currennt_exp`,`appointments`
       where `payment`.`id_payment` = `paymen_information`.`id_payment` and `paymen_information`.`id_bill` = `bills`.`id_bill` and bill_information.bills_id = `bills`.`id_bill` and bill_information.item_id = `items`.`id_item` and
       date_format(`payment`.`date`,'%Y-%m-%d') = date_format(`currennt_exp`.`_date`,'%Y-%m-%d') and
       date_format(`payment`.`date`, '%Y-%m-%d') = CURDATE()

and this is my table

  total       amount
  135.0000    135.0000
  165.0000    165.0000
  375.0000    375.0000
  300.0000    300.0000
  450.0000    450.0000

and this is the result and its very wrong

enter image description here

Tangentially Perpendicular
  • 5,012
  • 4
  • 11
  • 29
Arevan Shamal
  • 75
  • 5
  • Please read: https://stackoverflow.com/editing-help, and [edit] your post and, while editing, add some info on "very wrong". Why is it very wrong? What is expected? What did you try to solve this problem? Why did the search for a correct solution to this problem fail ? – Luuk Jun 29 '21 at 10:05
  • @Luuk I’m very new thanks for the advice i will edit my question, my problem is when i use >sum() the result should be 1425 but it returns 107100 instead – Arevan Shamal Jun 29 '21 at 10:13
  • Each join is multiplicative, so unless each join is 1:1 you'll end up with some duplicate records. Do you even need all those tables to be joined together to answer this query? – eggyal Jun 29 '21 at 10:19
  • more info on the correct way to do a JOIN is here: https://stackoverflow.com/questions/61279935/explicit-join-syntax – Luuk Jun 29 '21 at 10:22
  • @eggyal the problem is I wanna calculate all of them but as you say if the relationship between the tables are not one-to-one this will happen do you have any idea to work around this because I have data in the other tables and some of them I include to know how much I have of that item or bill so the calculation is right but it's way off, I calculate all of them by hand it was 1425 but is returns 107100 there is not even that much data in my database to do that :( please help – Arevan Shamal Jun 29 '21 at 10:41
  • @Luuk I used the correct way to JOIN them the number is reduced but still it's way of the correct result – Arevan Shamal Jun 29 '21 at 10:46
  • If you can change `SUM(payment.amount)` to `GROUP_CONCAT(payment_amount separator '+')`., you will see which numbers are added, and these numbers produce the total 107100. Conclusion `SUM()` is doing the right thing, but your query is broken... , and this can only be fixed when knowing the complete structure, and relations, for the tables involved. – Luuk Jun 29 '21 at 10:51
  • Unless I’m missing something, you’re joining each record of the `appointments` table to every payment? Also if there’s more than one payment or expense in a single day, they will each be joined with eachother. That’s two (but perhaps not the only) likely sources of duplication here. – eggyal Jun 29 '21 at 10:54
  • @Luuk I tried you idea this was the result ( '135.0000+135.0000+165.0000+165.0000+375.0000+375.0000+375.0000+375.0000+375.0000+375.0000+300.0000+300.0000+300.0000+300.0000+450.0000+450.0000+450.0000+450.0000+450.0000+450.0000+450.0000+450.0000' ) and know from this my query is so broken, I only have 5 numbers to sum but this shows more than that – Arevan Shamal Jun 29 '21 at 11:11

0 Answers0