CODEWITHSUNDEEP
mysql
I'll-Be-Back
I'll-Be-Back

Reputation: 10838

Count number of order and order lines report

I want to generate a report to count number of orders and order_lines by monthly.

In the orders table I have order_id and order_date fields

In the order_lines table, I have order_lines_id, order_id

I want a report to generate like this:

Year, Month, NumberOfOrders, NumbersOfLines 
2015   1           4             55
2015   2           3             6

I have tried the following SQL and didn't work how I wanted to be

SELECT 
  Year(orders.order_date) as Year,
  Month(orders.order_date) as Month,
  count(orders.order_id) as NumberOfOrders, 
  count(order_lines.order_lines_id) as NumbersOfLines, 
FROM `orders`               
  LEFT JOIN order_lines On order_lines.orders_id = order_lines.order_lines_id
group by sales.sales_id, order_lines.order_lines_id

Upvotes: 0

Views: 351

Answers (1)

sagi
sagi

Reputation: 40491

It didn't work for two reasons, 1 - because you grouped it by the wrong values. Think about it, you want to aggregate for each Year,Month . Why did you group by salesid and orderlines id?

And 2- your left join condition is not correct, you need to compare the common column in both tables which in this case is order_id

Anyways, try this:

SELECT 
  Year(t.order_date) as Year,
  Month(t.order_date) as Month,
  count(distinct t.order_id) as NumberOfOrders, 
  count(order_lines.order_lines_id) as NumbersOfLines, 
FROM `orders` t               
  LEFT JOIN order_lines On t.order_id = order_lines.order_id
group by Year(t.order_date), Month(t.order_date)

or with correlated query:

SELECT 
  Year(t.order_date) as Year,
  Month(t.order_date) as Month,
  (select count(distinct s.order_id)
      from orders s
      where Year(t.order_date) = Year(s.order_date)
      and Month(t.order_date) = Month(s.order_date))  as NumberOfOrders, 
  count(order_lines.order_lines_id) as NumbersOfLines, 
FROM `orders` t               
  LEFT JOIN order_lines On t.order_id = order_lines.order_id
group by Year(t.order_date), Month(t.order_date)

Upvotes: 1

Related Questions