CODEWITHSUNDEEP
mysqlsql
caboose0013
caboose0013

Reputation: 327

MySQL how to combine two rows into one

Hello stackoverflow,

I'm using mysql 8 with the following table:

create table usage_info
(
    id bigint auto_increment
        primary key,
    amount double not null,
    timestamp datetime null,
    type varchar null
);

example data:

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

I am able to get totals for each type for each day with 2 different methods

Method 1.

select total, type, date from (
select sum(u.amount) as total, u.type, DATE_FORMAT(u.timestamp, "%a-%c-%d-%y") as date
from usage_info u
group by DATE_FORMAT(u.timestamp, "%a-%c-%d-%y"), u.type) as temp;

results:

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

Method 2.

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, "%a-%c-%d-%y") as date
         from usage_info u
         group by DATE_FORMAT(u.timestamp, "%a-%c-%d-%y"), u.type
     ) as results;

gives:

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

But what I would like the results to look like is:

A        B       date
----------------------------
0.579    0.199   Tue-6-30-20
0.579    0.199   Wed-7-01-20

Thanks for your time!

Upvotes: 1

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Just use conditional aggregation:

select sum(case when type = 'A' then u.amount end) as total_a,
       sum(case when type = 'B' then u.amount end) as total_b,
       DATE_FORMAT(u.timestamp, "%a-%c-%d-%y") as date
from usage_info u
group by DATE_FORMAT(u.timestamp, '%a-%c-%d-%y');

Upvotes: 1

Related Questions