SELECT granule_list.brand_id, granule_list.type, AVG(price_table.price), AVG(price_table.percent_diff) FROM granule_list, price_table WHERE (price_table.brand_id=granule_list.brand_id) GROUP BY granule_list.type

It is necessary that WHERE work only for AVG(price_table.price) and AVG(price_table.percent_diff) The fact is that the rows in the price_list table with the brand_id field equal to the brand_id field in the granule_list table may not exist. But this does not mean that you do not need to display the string, you need to display at least the fields granule_list.brand_id, granule_list.type and leave empty AVG (price_table.price) AVG (price_table.percent_diff). And my code does not display such a line at all.

  • may not exist - and by what criterion then need to link the table? I'm not talking about the technical, but about the logical side of such a binding. - aleksandr barakin

1 answer 1

What you showed through WHERE is an internal connection (INNER JOIN), and to display records with missing links, an external connection (LEFT OUTER JOIN, RIGHT OUTER JOIN) is required. AVG () like other aggregate functions will ignore null values.

 SELECT granule_list.brand_id, granule_list.type, AVG(price_table.price), AVG(price_table.percent_diff) FROM granule_list LEFT JOIN price_table ON (price_table.brand_id=granule_list.brand_id) GROUP BY granule_list.type