MYSQL join return too many results

Question

So I'm trying to get some total numbers of different tables in the database. This is the query that I'm using

$this->db->select('
        c.*,
        SUM(rm.discount_value) as totalDiscount,
        SUM(a.status = 0) as totalVisits,
        SUM(a.status != 0) as totalAnnulations,
        SUM(r.treatment_price + r.arrangement_price + r.product_price) + c.start_rev as totalRevenue
    ')
        ->join('customers c','c.customer_id = a.customer_id','left')
        ->join('revenue r','r.customer_id = a.customer_id','left')
        ->join('remarks rm','rm.customer_id = a.customer_id','left')
        ->from('agenda a')
        ->where('a.user_id',$user_id)
        ->where('a.customer_id',$customer_id)
        ->group_by('a.customer_id');

This results in the following query.

SELECT `c`.*, 
       SUM(rm.discount_value) as totalDiscount,
       SUM(a.status = 0) as totalVisits, SUM(a.status != 0) as totalAnnulations, 
       SUM(r.treatment_price + r.arrangement_price + r.product_price) + c.start_rev as totalRevenue 
FROM (`agenda` a) 
       LEFT JOIN `customers` c ON `c`.`customer_id` = `a`.`customer_id`
       LEFT JOIN `revenue` r ON `r`.`customer_id` = `a`.`customer_id`
       LEFT JOIN `remarks` rm ON `rm`.`customer_id` = `a`.`customer_id` WHERE `a`.`user_id` = '9' AND `a`.`customer_id` = '4134' 
       GROUP BY `a`.`customer_id`

This query is returning 4810 visits, but there are only 74.

This returns too many results. Its like its not using that a.customer_id

Any help is welcome, thanks in advance.

Can you provide some data sample and result you have vs expected result please? — Alex, Jan 29 '18 at 16:54
Forget about the Laravel for now, and instead see: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Jan 29 '18 at 17:02
You did not provide anything I did ask for. I've asked about "actual data", "actual results" and expected result SAMPLE based on "actual data" you provide — Alex, Jan 29 '18 at 17:04
most likely because of your sum, try having a select sum instead of directly summing the values, something among the lines: (select SUM(rm.discount_value) from remarks rm where rm.id = remarks.id) as totalDiscount . where remarks.id is your table on the upper query — abr, Jan 29 '18 at 17:11
@Strawberry : Does not make a difference if I use a or c. Alex : It simply returns a row, username,firstname,lastname,visits,revenue.... I'm not exactly sure what you are asking for. I cant simply paste raw results here because the data is actually beeing used in production. Only the field names are different there — Zenel Shabani, Jan 29 '18 at 17:11
@ZenelShabani move `->from('agenda a')` next to select and check — Abdulla Nilam, Jan 29 '18 at 17:14
@abr I'm still editing it, but I think thats the right way to do it. I'll keep u updated :) — Zenel Shabani, Jan 29 '18 at 17:15
Your `GROUP BY` doesn't make sense. Maybe if you fix it, it will also resolve your problem. Your query, by itself, is not a valid query. It will error out. — Eric, Jan 29 '18 at 17:50

score 0 · Answer 1 · answered Jan 29 '18 at 17:47

0

You are using GROUP BY with a column that does not appear in the SELECT. See how to use GROUP BY.

answered Jan 29 '18 at 17:47

slaakso

8,331
2
16
27

MYSQL join return too many results

1 Answers1