Geek asks and answers

Mysql joining and amount double the result

I have an income table as

title_id     revenue    cost
   1            10        5
   2            10        5
   3            10        5
   4            10        5
   1            20        6                    
   2            20        6
   3            20        6
   4            20        6

when i execute this request

SELECT SUM(revenue),SUM(cost)
FROM revenue
GROUP BY revenue.title_id

he produces the result

title_id     revenue    cost
   1            30        11
   2            30        11
   3            30        11
   4            30        11

which is fine, now I want to combine the final result with another table that has a structure like this

title_id     interest    
   1            10        
   2            10        
   3            10        
   4            10        
   1            20                            
   2            20        
   3            20        
   4            20

when I do a join with an aggregate function like this

SELECT SUM(revenue),SUM(cost),SUM(interest)
FROM revenue
LEFT JOIN fund ON revenue.title_id = fund.title_id
GROUP BY revenue.title_id,fund.title_id

he doubles the result

title_id     revenue    cost    interest
   1            60        22       60
   2            60        22       60
   3            60        22       60
   4            60        22       60

I can’t understand why it is twice as much, please help

+5
source share
3 answers

Its doubling, because you have the title repeated in the tables of funds and income. This multiplies the number of entries in which it matches. It's pretty easy to see if you remove aggregated functions and look at the raw data. Look here

- .

SELECT R.title_id, 
       R.revenue, 
       R.cost, 
       F.interest 
FROM   (SELECT title_id, 
               Sum(revenue) revenue, 
               Sum(cost)    cost 
        FROM   revenue 
        GROUP  BY revenue.title_id) r 
       LEFT JOIN (SELECT title_id, 
                         Sum(interest) interest 
                  FROM   fund 
                  GROUP  BY title_id) f 
              ON r.title_id = F.title_id 


| TITLE_ID | REVENUE | COST | INTEREST |
----------------------------------------
|        1 |      30 |   11 |       30 |
|        2 |      30 |   11 |       30 |
|        3 |      30 |   11 |       30 |
|        4 |      30 |   11 |       30 |

+11

, , . , (fund) , LEFT JOIN.

SELECT  b.title_id,
        b.TotalRevenue,
        b.TotalCost,
        d.TotalInterest
FROM 
(
    SELECT   a.title_id, 
             SUM(a.revenue) TotalRevenue, 
             SUM(a.cost) TotalCost
    FROM     revenue a
    GROUP BY a.title_id
) b LEFT JOIN
(
    SELECT   c.title_id, 
             SUM(a.interest) TotalInterest
    FROM     fund c
    GROUP BY c.title_id
) d ON b.title_id = d.title_id
+3

There are two rows in the income table for each title_id.

+1
source

More articles:


All Articles