CODEWITHSUNDEEP
phpmysqlcodeigniter
Jaymin
Jaymin

Reputation: 1661

Fetch Products Count for specific week in 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.

DEMO OUTPUT

Upvotes: 0

Views: 29

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133380

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
  ;

Upvotes: 1

Related Questions