Custom report from SQL query

Question

I want to get order total of the month January 2019 for complete orders

I need to know tables related to this query, or if someone can share query that would be great.

• fmsthird: order total, you mean the total number of orders for year 2019 or the total sum of amount of all the order for the specific month?
• Muhammad Anas: I need sum amount

Answer 1

Your questions seems double meaning for me, so here I'll post my answer assuming:

You want to get the Total Numbers of order for the specific month, below query should work

SELECT count(entity_id) FROM sales_order where state='complete' and status='complete' and month(created_at) = '01' and year(created_at) = '2019'; 

You want to get the total sum amount you can try this (This will filter only order that are completed):

SELECT sum(base_grand_total) FROM sales_order where state='complete' and status='complete' and month(created_at) = '01' and year(created_at) = '2019';

• Muhammad Anas: It worked. Thanks

Answer 2

Here is query which will give you order total of completed order's for month jan 2019:

SELECT sum(base_grand_total) FROM sales_order
WHERE created_at >='2018-12-31 23:59:59.000' AND created_at <= '2019-02-31 
23:59:59.000' AND status='complete'

• Muhammad Anas: Thanks Mohit, but it is showing warning that incorrect datetime format
• Muhammad Anas: Thanks for answer. +1