Mysql Multiple Left Join And Sum not Working Correctly

Question

I am new at joins. I want to get sum of debts and incomes on mysql. But I am facing with a problem. The problem is sum works more than normal. Here is query

Select uyeler.*,
sum(uye_gider.tutar) as gider,
sum(gelir.tutar) as gelir
from uyeler
LEFT JOIN gelir on gelir.uye=uyeler.id
LEFT JOIN uye_gider on uye_gider.uye=uyeler.id
group by uyeler.id

First:

If I dont write group by it gives me only first row.

Does this work like this?

Main problem:

I have :

-2 row 'uye'(user)

-3 row 'gelir'(income)

-1 row 'uye_gider'(debt) value is 25

But when I execute this query the value of gider is 75.

I think its sum('uye_gider.tutar') is working 3 times because of 'gelir.tutar'

What am I doing wrong?

------Tables------

CREATE TABLE IF NOT EXISTS `gelir` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tarih` date NOT NULL,
  `uye` int(11) NOT NULL,
  `tutar` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `uyeler` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ad` varchar(15) NOT NULL,
  `soyad` varchar(15) NOT NULL,
  `tc` varchar(11) NOT NULL,
  `dogum` date NOT NULL,
  `cep` int(11) NOT NULL,
  `eposta` varchar(50) NOT NULL,
  `is` int(11) NOT NULL,
  `daire` int(11) NOT NULL,
  `kan` varchar(5) NOT NULL,
  `web` varchar(12) NOT NULL,
  `webpw` varchar(100) NOT NULL,
  `tur` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `uye_gider` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uye` int(11) NOT NULL,
  `tutar` float NOT NULL,
  `gider` int(11) NOT NULL,
  `aciklama` text COLLATE utf8_bin,
  PRIMARY KEY (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;
---End Tables---

I'm voting to close this as unclear what you're asking. Please provide some sample data and/or an SQL Fidlde that shows the expected results. — AdamMc331, May 27 '15 at 19:24
Tables added. expected result for gider is 25. but I got 75. — Yavuz Selim, May 27 '15 at 19:42

score 1 · Accepted Answer · edited May 23 '17 at 11:43

first

Group by, groups sets of data according to the field you've picked. If you want a total sum of an entire table you should only want one row and it should not be grouped by anything. If you want to group your sums by a value, ie. the 'animals' table

| id | animal | food_eaten |
| 1  | dog    | 10         |
| 2  | cat    | 13         |
| 3  | dog    | 10         |


select animal, sum(food_eaten) as total_food_eaten from animals group by animal;

will give you

| animal | total_food_eaten |
| dog    | 20               |
| cat    | 13               |

That is how group by works. It sections your queries by a field of non-unique values that you pick. so,

 select sum(food_eaten) as total_food_eaten from animals;

will give you

 |total_food_eaten|
 | 33             |

second

A left join will return all your left tables values regardless of matches and will join to any right join tables with values that match. What I am sure of is the fact that you have three income rows associating with one user row. When you do a left join this generates three matching left join rows. When you then left join the debt row to these three rows it can associate to all three, since the ID matches. This is what is giving you a three-pete. I suggest, if you are only looking for the sum for both I suggest splitting up the queries seeing as the income and debt tables want to have no association with each other in these tables.

This is a likely answer to help you along the way.

Multiple select statements in Single query

Mysql Multiple Left Join And Sum not Working Correctly

1 Answers1