title: daily sales report with PHP Mysql CASE
body: <p>I need to get the daily sales report based on below query..</p>
  
  <p>This is my Table structure</p>
  
  <pre><code>orderid resid   payment_type    ordertotalprice orderdate
  1       10      cod             50              2016-06-14 
  2       10      cod             10              2016-06-14
  3       10      creditcard      40              2016-06-14 
  4       10      cod             30              2016-06-14
  5       10      creditcard      20              2016-06-14 
  6       10      cod             10              2016-06-14
  7       10      creditcard      20              2016-06-14 
  8       11      cod             10              2016-06-14
  </code></pre>
  
  <p>I need the output result below using above table.</p>
  
  <pre><code>resid total_orders_cnt total_sales total_orders_cash total_sales_cash   total_orders_cc total_sales_cc
  10    7                180         4                 100                    3               80
  11    1                10          1                 10                     0               0
  </code></pre>
  
  <p>I'm using below query, But its not getting exact result what i want.</p>
  
  <pre><code>SELECT COUNT(orderid) AS total_orders_cnt, SUM(ordertotalprice) AS total_sales,
          SUM(CASE WHEN (payment_type= 'cod') THEN 1 ELSE 0 END) AS total_orders_cash,
          CASE WHEN (payment_type= 'cod') THEN SUM(ordertotalprice) ELSE 0 END AS total_sales_cash,
          SUM(CASE WHEN (payment_type = 'creditcard') THEN 1 ELSE 0 END) total_orders_cc,
          CASE WHEN (payment_type = 'creditcard') THEN SUM(ordertotalprice) ELSE 0 END AS total_sales_cc
  FROM order WHERE resid='10' AND orderdate = '2016-06-14';
  </code></pre>
  
  <p>So pls anyone help me.</p>
  
  <p>Thanks in advance</p>