title: Grouping Multiple Columns
body: <p>I have the following query:</p>
  
  <pre><code>SELECT od.product_id, od.product_name, od.product_quantity, od.product_price,
          o.id_address_delivery, a.id_country, a.id_state, s.name
  FROM `ps_order_detail` od
  LEFT JOIN ps_orders AS o ON od.`id_order` = o.`id_order`
  LEFT JOIN `ps_address` AS a ON o.`id_address_delivery` = a.`id_address`
  LEFT JOIN `ps_state` AS s ON a.id_state = s.id_state
  ORDER BY o.`date_add` DESC
  LIMIT 100;
  </code></pre>
  
  <p>Where it returns data like the following :</p>
  
  <p>product_id | product_name | product_qty | product_price | id_state</p>
  
  <p>I'd like to know the best seller (in terms of quantity) on each state. For example:</p>
  
  <p>State A : best seller is Product A (500 sold quantity)
  State B : best seller is Product C (150 sold quantity)
  State C : best seller is Product A (100 sold quantity)</p>
  
  <p>At the moment i am thinking of doing a JOIN between 2 columns, but on second thought this requires a 'pivot' like features.</p>
  
  <p>I know how to get the total sold quantity for each product</p>
  
  <pre><code>SELECT od.product_id, od.product_name, COUNT(od.product_id) AS 'total'
  FROM `ps_order_detail` od
  GROUP BY od.product_id
  ORDER BY od.product_id DESC
  LIMIT 100;
  </code></pre>
  
  <p>I also know the total sold quantity for each state, but i am not sure how to combine both to achive what i need. I'd appreciate any help. Thanks</p>