MySQL how to sum values when using 3 tables

Question

This issue has been bothering me for some time now. I have made the statement and it works as I get all my data out the way I want it.

CREATE VIEW `pizza` AS
SELECT 
    `table1`.`id` AS `id`,
    `table1`.`name` AS `name`,
    `table2`.`name` AS `rb_name`,
    `table3`.`netto` AS `min`,
    `table3`.`netto` AS `max`
FROM
    ((`table1`
    JOIN `table3` ON ((`table1`.`id` = `table3`.`id`)))
    JOIN `table2` ON ((`table2`.`t1_id` = `table3`.`t1_id`)))

Now the problem is that i want to SUM the min and max value in the table. But when I do that I go from having a list of results to just having 1 result. This is the code i add to the SELECT statements where I have the min and max value:

        SUM((`table3`.`netto` - `table3`.`tolerance`)) AS `min`,
        SUM((`table3`.`netto` + `table3`.`tolerance`)) AS `max`,

I don't know how to work around it. My database structure is as follows:

Table 1 has columns "rb_id" and "rb_name".

Table 2 has columns "id", "name" and others that aint beeing used here.

Table 3 connects them both by having "rb_id" and "id" with the "netto" and "tolerance" values

what does table3 contain? can you explain better your database structure and how table3 is related to table1 and table2? — fthiella
– fthiella, Commented May 1, 2016 at 11:47

ScaisEdge · Accepted Answer · 2016-05-01 12:03:43Z

1

I think you need a group by clause for a aggregation function (sum)

SELECT 
    `table1`.`id` AS `id`,
    `table1`.`name` AS `name`,
    `table2`.`name` AS `rb_name`,
    SUM((`table3`.`netto` - `table3`.`tolerance`)) AS `min`,
    SUM((`table3`.`netto` - `table3`.`tolerance`)) AS `max`,
FROM
    ((`table1`
    JOIN `table3` ON ((`table1`.`id` = `table3`.`id`)))
    JOIN `table2` ON ((`table2`.`t1_id` = `table3`.`t1_id`)))
GROUP BY   `table1`.`id` ,    `table2`.`name` ;

answered May 1, 2016 at 12:03

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kingfox Over a year ago

this was what I needed. It now shows me a list of result with the SUM of min and max for every item. Thank you !!

PerlDuck Over a year ago

@Kingfox A question: What's the difference between your `min` and `max` columns? They are calculated the same. A typo?

Kingfox Over a year ago

Yeah its a typo, but I first realised that after it was working :) I have edited the post, so it doesnt contain it any more.

Collectives™ on Stack Overflow

MySQL how to sum values when using 3 tables

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related