title: MySQL how to combine two rows into one
body: <p>Hello stackoverflow,</p>
  <p>I'm using mysql 8 with the following table:</p>
  <pre><code>create table usage_info
  (
      id bigint auto_increment
          primary key,
      amount double not null,
      timestamp datetime null,
      type varchar null
  );
  </code></pre>
  <p>example data:</p>
  <pre><code>id  amount  timestamp             type
  --------------------------------------
  1   0.123   2020-06-30 12:40:54   A
  2   0.098   2020-06-30 15:47:51   B
  3   0.456   2020-06-30 16:40:54   A
  4   0.101   2020-06-30 17:47:51   B
  5   0.123   2020-07-01 12:40:54   A
  6   0.098   2020-07-01 15:47:51   B
  7   0.456   2020-07-01 16:40:54   A
  8   0.101   2020-07-01 17:47:51   B
  </code></pre>
  <p>I am able to get totals for each type for each day with 2 different methods</p>
  <p>Method 1.</p>
  <pre><code>select total, type, date from (
  select sum(u.amount) as total, u.type, DATE_FORMAT(u.timestamp, &quot;%a-%c-%d-%y&quot;) as date
  from usage_info u
  group by DATE_FORMAT(u.timestamp, &quot;%a-%c-%d-%y&quot;), u.type) as temp;
  </code></pre>
  <p>results:</p>
  <pre><code>total    type    date
  ---------------------
  0.579    A       Tue-6-30-20
  0.199    B       Tue-6-30-20
  0.579    A       Wed-7-01-20
  0.199    B       Wed-8-01-20
  </code></pre>
  <p>Method 2.</p>
  <pre><code>select case
             when type = 'A' then total
             end as A,
         case
             when type = 'B' then total
             end as B,
         date
  from (
           select sum(u.amount) as total, u.type, DATE_FORMAT(u.timestamp, &quot;%a-%c-%d-%y&quot;) as date
           from usage_info u
           group by DATE_FORMAT(u.timestamp, &quot;%a-%c-%d-%y&quot;), u.type
       ) as results;
  </code></pre>
  <p>gives:</p>
  <pre><code>A        B       date
  ----------------------------
  0.579    null    Tue-6-30-20
  null     0.199   Tue-6-30-20
  0.579    null    Wed-7-01-20
  null     0.199   Wed-7-01-20
  </code></pre>
  <p>But what I would like the results to look like is:</p>
  <pre><code>A        B       date
  ----------------------------
  0.579    0.199   Tue-6-30-20
  0.579    0.199   Wed-7-01-20
  </code></pre>
  <p>Thanks for your time!</p>