MySql查询 - 跨计数的多个表的数学

时间:2022-05-24 07:50:23

I have 2 tables, one has a list of 'products sold' the other is a list of 'product prices'.

我有2个表,一个有'产品销售'列表,另一个是'产品价格'列表。

**SALES**
product_1
product_1
product_1
product_2

**PRICES**
Product_1 | 10
product_2 | 20

I need to count each products and multiply that by its cost.

我需要计算每个产品并将其乘以其成本。

The query should give a result in the following format:

查询应该以下列格式给出结果:

NAME_________|______TOTAL

PRODUCT1_____|______30
PRODUCT2_____|______20

Any help is greatly appreciated!

任何帮助是极大的赞赏!

1 个解决方案

#1


3  

Join both tables using their linking column (specifically the foreign key), use aggregate function SUM and grouped them by their name.

使用其链接列(特别是外键)连接两个表,使用聚合函数SUM并按名称对它们进行分组。

SELECT  a.name, SUM(b.price) as TotalPrice
FROM    sales a
        INNER JOIN prices b
            on a.name = b.name
GROUP BY a.name

#1


3  

Join both tables using their linking column (specifically the foreign key), use aggregate function SUM and grouped them by their name.

使用其链接列(特别是外键)连接两个表,使用聚合函数SUM并按名称对它们进行分组。

SELECT  a.name, SUM(b.price) as TotalPrice
FROM    sales a
        INNER JOIN prices b
            on a.name = b.name
GROUP BY a.name