如何获取特定日期的产品数量总和? [英] How to get sum of quantity of products from a specific date?

问题描述

表 1 销售额

id   |  date
=================
1    |  10 july
2    |  10 july
3    |  20 july

表 2 产品

sale_id   |  product_name | quantity
====================================
1         | cold drink    | 2
1         | Samosa        | 3
1         | Burger        | 1
2         | Burger        | 4
3         | Shwarma       | 1

我想获取特定日期的产品名称及其销售数量例如7 月 10 日，有两个 id 为 1,2 的销售和该日期销售的产品是

I want to get product name and their quantity sold on a specific date for example on july 10 there two sales with id 1,2 and product sold on that date are

cold drink  2
samosa      3
Burger      5 (1 sold in sale 1 and 4 sold in sale 2)

我如何在 mysql 中做到这一点?

How do i do that in mysql?

我试过使用从 Products GROUP BY product_name 中选择 product_name, SUM(quantity)但是这个查询给了我我想要的特定日期的总和，我如何关联这两个表.

i have tried using Select product_name, SUM(quantity) From Products GROUP BY product_name but this query give me all the sum i want sum from a specific date , how do i relate these two tables.

推荐答案

如果你想要全天的数据，那么使用这个

If you want data for all days then use this

SELECT products.product_name, sales.date, sum(products.quantity) 
FROM products LEFT JOIN sales 
ON products.sale_id = sales.id group by products.product_name, sales.date;

如果你想要特定日期的数据，你可以使用这个:

If you want the data on date specific the you can use this:

SELECT products.product_name, sales.date, sum(products.quantity) 
FROM products LEFT JOIN sales 
ON products.sale_id = sales.id WHERE sales.date = '10 july' group by products.product_name, sales.date;

演示

这篇关于如何获取特定日期的产品数量总和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！