Kingfox Kingfox - 7 months ago 13
SQL Question

MySQL how to sum values when using 3 tables

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 dont 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

Answer

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` ;
Comments