在mysql中获取特定周的产品计数

问题描述：

I have 3 tables

Orders
id | name | order_date | total
------------------------------
1  | ABCD | 2017-03-29 | 60
2  | KJHS | 2017-03-29 | 80
3  | HGGG | 2017-03-30 | 90

item_orders

id | order_id | item_id
-----------------------
1  | 1        | 1
2  | 1        | 2
3  | 2        | 1
4  | 2        | 2

items
id  | item_name  | desc | price |
---------------------------------
1   | Brown      | jdsa | 20    
      Cardigan XL
---------------------------------
2   | Red Socks  | sada | 30
---------------------------------

My Query which I have wrote is

SELECT orders.*, item_orders.item_id,items.item_name,items.description,items.price
FROM ((orders
INNER JOIN item_orders ON orders.id = item_orders.item_id)
INNER JOIN items ON item_orders.item_id = items.id)
WHERE WEEKOFYEAR(orders.order_date)=WEEKOFYEAR(NOW());

Now I wanted to bring item_count total from orders table of current week with its total. Something like these shows current week stats

BrownCardigan XL    12  $200
Red Socks           15  $350

Something like these. I am bringing data from orders table by above query and Now I am stuck of how to count the items from order table and sum of prices for the current week.

我有3个表 p>

  Orders 
id | 名字|  order_date | 总计
 ------------------------------ 
1 |  ABCD |  2017-03-29 |  60 
2 |  KJHS |  2017-03-29 |  80 
3 |  HGGG |  2017-03-30 |  90 
 
item_orders 
 
id |  order_id |  item_id 
 ----------------------- 
1 |  1 |  1 
2 |  1 |  2 
3 |  2 |  1 
4 |  2 |  2 
 
items 
id |  item_name |  desc | 价格| 
 --------------------------------- 
1 | 布朗|  jdsa |  20 
 Cardigan XL 
 --------------------------------- 
2 | 红袜子|  sada |  30 
 --------------------------------- 
 代码>  PRE> 
 
  我写的My Query是 p> 
 
 
  SELECT orders。*，item_orders.item_id，items.item_name，items.description，items.price 
FROM（（orders）  nINNER JOIN item_orders ON orders.id = item_orders.item_id）
INNER JOIN items ON item_orders.item_id = items.id）
WHERE WEEKOFYEAR（orders.order_date）= WEEKOFYEAR（NOW（））; 
  code>  pre  > 
 
 现在我想把当前周订单表中的item_count总计及其总数。
这些显示当前周数据 p> 
 
 
  BrownCardigan XL 12  $ 200 
Red袜子15 $ 350 
  code>  pre> 
 
 这样的东西。
我通过上面的查询从订单表中提取数据现在我不知道如何计算来自 订单表和当周价格总和。 p> 
 
 
     p> 
  div>

答

you could use aggreagation function and group by

  SELECT items.item_name, sum(orders.total), sum(orders.total*items.price)
  FROM orders
  INNER JOIN item_orders ON orders.id = item_orders.item_id
  INNER JOIN items ON item_orders.item_id = items.id
  WHERE WEEKOFYEAR(orders.order_date)=WEEKOFYEAR(NOW()
  GROUP BY  items.item_name
  ;

在mysql中获取特定周的产品计数

相关推荐