title: Group count product ids in sql
body: <p>I am fairly new to sql so I apologies for if this is a nooby question but I have been racking my brain for a while and search google with no answers so this is my last resort.</p>
  
  <p>I have been trying to make a top selling page on my personal site which sells the top selling item for the last 30 days.</p>
  
  <p>Withing the database there are 2 tables for this: products and products orders</p>
  
  <p>The products how total sales of all time, review count of all time and other relevant information but the product orders have the product sale date.</p>
  
  <p>So the way I see it the only thing products is good for is the title, image and price.</p>
  
  <p>So I now look at the products orders and this has the product id and the date sold.</p>
  
  <p>So my question is how to i group the ids,</p>
  
  <p>for instance if I had:</p>
  
  <pre><code>productid = 42, datesold = 2013-11-08 21:11:10
  productid = 11, datesold = 2013-11-08 21:09:10
  productid = 42, datesold = 2013-11-08 21:06:10
  productid = 2,  datesold = 2013-11-07 21:11:10
  productid = 11, datesold = 2013-11-06 21:11:10
  </code></pre>
  
  <p>How would I return it so it basically says</p>
  
  <pre><code>productid = 42 (sold 2 times)
  productid = 11 (sold 2 times)
  productid = 2 (sold 1 time)
  </code></pre>
  
  <p>Can something like this even be done?</p>
  
  <p>Also I think I have the left join which shows it but still cant get the logic on the group counting</p>
  
  <p>Here is my left join logic, please bare in mind it will be pdo, this is just showing the query before I prepare statement it ;)</p>
  
  <p>$todaysdate = date('Y-m-d h:i:s', strtotime("now - 30 days ago"));</p>
  
  <pre><code>SELECT * FROM product_order LEFT JOIN product ON product_order.productId=product.id
  WHERE product_order.modifiedDate &gt;= $todaysdate AND product_order.status = 2 
  ORDER BY product_order.modifiedDate DESC;
  </code></pre>
  
  <p>Thanks in advance for any help given :)</p>
  
  <p>EDIT::::::::::::::::::::::::::::::::::::</p>
  
  <p>Ive changed it but for some reason its throwing up a major error:</p>
  
  <p>SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '15:14:13 AND product_order.status = 2 AND product.status = 1 GROUP BY product_o' at line 4
  Stack Trace:</p>
  
  <p>Here is the sql query:</p>
  
  <pre><code>$todaysdate = date('Y-m-d G:i:s', strtotime("now - 30 days ago"));
  
  SELECT product.id, product.name, product.picture, product.mainImage, product_option_detail.price, COUNT(product_order.productId) AS NumberOfOrders FROM product_order 
  RIGHT JOIN product ON product_order.productId=product.id 
  INNER JOIN product_option_detail ON product_order.productId=product_option_detail.productId 
  WHERE product_order.modifiedDate &gt;= {$createddate} AND product_order.status = 2 AND product.status = 1  
  GROUP BY product_order.productId
  ORDER BY NumberOfOrders DESC;
  </code></pre>