时间戳

首页 > 解决方案 > Mysql SUM 函数，按周加入和分组

mysql - Mysql SUM 函数，按周加入和分组

问题描述

我有两张桌子

第一桌Sales

   id   invoice_date      points 
------------------------------------------------
    1    2020-07-01       5
    2    2020-07-01       30
    3    2020-07-02       1
    4    2020-07-03       10
    5    2020-07-04       2.5
    6    2020-07-05       35
    7    2020-07-06       50
    8    2020-07-07       30.5

第二张桌子sales_details

id    sales_id      item        mrp     qty 
---------------------------------------------
1        1           A          200     1
2        1           B          300     2
3        2           C          2000    1
4        2           A          200     2
5        2           AB         800     3
6        3           C          2000    1
7        3           D          100     2
8        4           A          200     3
9        5           C          2000    4
10       5           D          100     1
11       6           B          300     1
12       7           A          200     1
13       8           B          300     1

我想通过分组周查询以获取结果item计数、mrp总数、qty表中的总数sales_details和表中的points总数。sales我尝试使用以下查询，但总points列错误

SELECT sum(B.qty) as item_count,sum(B.mrp*B.qty) as mrp, DATE_ADD( DATE(A.invoice_date), INTERVAL (7 - DAYOFWEEK( A.invoice_date )) DAY) week_ending,sum(points) as points from sales A inner join
 sales_details B on A.id=B.sales_id where A.invoice_date>='2020-07-04' and A.invoice_date<='2020-07-07'  GROUP BY week_ending

并得到了结果，points值是错误的

item_count   mrp      week_ending        points
-----------------------------------------------
    5        8100      2020-07-04          5
    3        800       2020-07-11         115.5

我想要的实际结果是

item_count   mrp      week_ending        points
-----------------------------------------------
    5        8100      2020-07-04          48.5
    3        800       2020-07-11         115.5

请帮我解决这个问题。

标签： mysql

解决方案

您只需要检查您的日期过滤器。另外，我认为您的预期输出看起来不像您的意思。

SELECT sum(B.qty) as item_count,sum(B.mrp*B.qty) as mrp, 
       DATE_ADD( DATE(A.invoice_date), INTERVAL (7 - DAYOFWEEK( A.invoice_date )) DAY) week_ending,
       sum(points) as points 
FROM sales A  
INNER JOIN sales_details B ON A.id=B.sales_id 
WHERE A.invoice_date>='2020-07-01'  -->Change this date to include all dates of week ending 2020-07-04. You are only including the 4th 
AND A.invoice_date<='2020-07-07'  
GROUP BY week_ending

推荐阅读