title: How can I get the count of each row using an inner join? Getting an error that a column doesn't exist
body: <p>I am trying to get the sum of order_sum column as well as the sum of order_sum where the payment_method is cash and card seperately. But I'm getting an error that a column does not exist.</p>
  
  <p>Any help would be much appreciated!</p>
  
  <blockquote>
    <p>Unknown column 'table2.id' in 'on clause'</p>
  </blockquote>
  
  <p>Here is my <code>orders</code> table</p>
  
  <pre><code>+----+-----------+----------------+------------+---------------------+
  | id | order_sum | payment_method | is_deleted | created_at          |
  +----+-----------+----------------+------------+---------------------+
  | 1  | 123       | cash           | 0          | 2019-12-15 12:05:38 |
  | 2  | 456       | card           | 0          | 2019-12-16 17:45:11 |
  | 3  | 789       | cash           | 0          | 2019-12-16 20:14:59 |
  +----+-----------+----------------+------------+---------------------+
  </code></pre>
  
  <p>And here is my query so far..</p>
  
  <pre><code>SELECT
      sum(order_sum) as order_sum,
      count(*) as count,
      DATE_FORMAT(created_at, '%Y-%m-%e') as date,
      table2.cash_sum
  FROM
      orders
  INNER JOIN (
      SELECT
          sum(order_sum) as cash_sum,
          DATE_FORMAT(created_at, '%Y-%m-%e') as date
      FROM
          orders
      WHERE payment_method = 'cash'
      GROUP BY
          date
  ) as table2 on orders.id = table2.id
  where
      is_deleted = 0
  group by
      date 
  order by date
  </code></pre>
  
  <p>The desired result would look like this</p>
  
  <pre><code>+------------+-----------+----------+----------+
  | date       | order_sum | cash_sum | card_sum |
  +------------+-----------+----------+----------+
  | 2019-12-15 | 579       | 123      | 0        |
  | 2019-12-16 | 1245      | 789      | 456      |
  +------------+-----------+----------+----------+
  </code></pre>