LEFT JOIN alters COUNT results

gwerner

I have a query where I'm trying to do a COUNT and a SUM with a LEFT JOIN. The LEFT JOIN is adding it's results to total count. I'm sure this is the nature of doing a LEFT JOIN. But, I would like to know is there a way around this? Or, do I need to run a separate query to get accurate results? The SUM returns normal.

What I'm trying to do is get the number of orders for each month for whatever year that is being accessed.

$query1 = "SELECT orderinfo_date, (SELECT COUNT(*) FROM orderinfo WHERE WHERE date_format(`orderinfo_date`, '%Y') = '$yearId') AS monthTotal, ROUND(SUM(orderitems_price * orderitems_quantity), 2) AS monthSum
		   FROM orderinfo
		   LEFT JOIN orderitems ON orderinfo.orderinfo_id = orderitems.orderitems_oid
		   WHERE date_format(`orderinfo_date`, '%Y') = '$yearId'
		   GROUP BY date_format(`orderinfo_date`, '%m')";
$result1 = mysql_query($query1);

On a side note, I have another query that does the same thing. But, this one works fine. It has one additional WHERE clause. The month is being supplied to the query. Where as, the query above does not.

$query1 = "SELECT company_name, company_id, orderinfo_date, (SELECT COUNT(*) FROM orderinfo WHERE orderinfo_coid = company_id AND date_format(`orderinfo_date`, '%m') = '$monthId' AND date_format(`orderinfo_date`, '%Y') = '$yearId') AS monthTotal, ROUND(SUM(orderitems_price * orderitems_quantity), 2) AS monthSum
		   FROM company
		   LEFT JOIN orderinfo
		   ON company.company_id = orderinfo.orderinfo_coid
		   LEFT JOIN orderitems
		   ON orderinfo.orderinfo_id = orderitems.orderitems_oid
		   WHERE date_format(`orderinfo_date`, '%m') = '$monthId' AND date_format(`orderinfo_date`, '%Y') = '$yearId'
		   GROUP BY company_id";
$result1 = mysql_query($query1);

Any insight is appreciated. Thanks.

Lars_Berg

select orderinfo_date, 
       count(orderitems.orderitems_oid) as monthTotal, 
       round(sum(orderitems_price*orderitems_quantity),2) as monthSum
  from orderinfo
  left join orderitems 
    on orderinfo.orderinfo_id = orderitems.orderitems_oid
 where year(orderinfo_date) = $yearId
 group by month(orderinfo_date)

gwerner

This gives me the same results. The orderitems could have multiple oid's. which would throw the count higher. The true unique value is orderinfo_id.

I tried swapping the values inside the count, but that gives the same results as well. Any other ideas?

Lars_Berg

Give some sample data and the expected result.

gwerner

Here is an example data set. The result I'm trying to get is 3. If i do the count without the left join I get 3, since there are 3 orders. When I do the left join I get 10. Which is the 3 orders plus the 7 items associated to the orders.

This data set only show orders for a single month. The actual table will have orders for all 12 months. I want to be able to get the results for each month separately for the year.

orderinfo_id		orderinfo_first		orderinfo_last		orderinfo_address		orderinfo_date
101			John			Smith			123 Any Street			2010-03-11
102			Susie			Que			321 Abc Drive			2010-03-15
103			Carol			Smith			123 Circle			2010-03-26


orderitems_id		orderitems_oid		orderitems_quanity		orderitems_price
23			101			1				5.99
24			101			2				12.99
25			102			1				7.99
26			103			1				4.99
27			103			1				4.99
28			103			1				9.99

I'm using a while loop to display the results. So,

Month			Total Orders			Total Sales
January			0				0.00
February		0				0.00
March			3				59.93