Devesh Agrawal Devesh Agrawal - 5 months ago 7
MySQL Question

Count is not returning correct value

I have 2 tables.

table_orders - order_id, order_datetime
table_order_details - order_id, product_sell_price, quantity


I want to get total sell amount month wise with number of orders in that month. This is my query.

select SUM(OD.product_sell_price * OD.quantity) Total,
count(O.order_id) Count, MONTH(order_datetime) Month,
MONTHNAME(order_datetime) MonthName, YEAR(order_datetime) YEAR
from
table_orders O
INNER JOIN
table_order_details OD
ON O.order_id = OD.order_id
group by MONTH(order_datetime), YEAR(order_datetime)
order by order_datetime;


All values are correct except number of orders of that month. I think its giving the count of order_id after INNER JOIN of both tables.

Can anyone help me on this?

Answer

Just use COUNT (DISTINCT column) which will make sure you are counting only unique order ID's :

select SUM(OD.product_sell_price * OD.quantity) Total,
       count(DISTINCT O.order_id) Count,
       MONTH(order_datetime) Month,
       MONTHNAME(order_datetime) MonthName,
       YEAR(order_datetime) YEAR
from table_orders O
INNER JOIN table_order_details OD 
 ON O.order_id = OD.order_id
group by MONTH(order_datetime), YEAR(order_datetime)
order by order_datetime;