CODEWITHSUNDEEP
mysqlsqlsum
adam Kearsley
adam Kearsley

Reputation: 999

MySql Query - Math across multiple tables with count

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

**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!

Upvotes: 1

Views: 776

Answers (1)

John Woo
John Woo

Reputation: 263893

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

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

Upvotes: 3

Related Questions