Devesh Agrawal Devesh Agrawal - 1 year ago 59
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 Source

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;