Mysql - Select - how to group two different values into a single value

Question

I would like help on a subject.

I have a query that lists requests(orders), and each request has its status, for example: 'confirmed', 'refused' and 'unmarked', I would like to create a Select that unites these status, for example, 'confirmed' is a status, 'refused' and 'unmarked' as being a single status, that is, a way of grouping two different values into one

Query:

SELECT monthname(o.processed_timestamp) AS Mês,
       o.status,
       count(1)
FROM orders_received.partner_commerce_item o
WHERE o.status IN ('confirmado',
                   'recusado',
                   'naoconfirmado')
GROUP BY month(o.processed_timestamp),
         o.status;

The way this will be listed the orders in three different status, wanted to create something like:

SELECT monthname(o.processed_timestamp) AS Mês,
       o.status,
       count(1)
FROM orders_received.partner_commerce_item o
WHERE o.status = 'confirmado'
OR    o.status in ('recusado','naoconfirmado') as recusados                   
GROUP BY month(o.processed_timestamp),
         o.status;

But this way presents syntax error

I’ve tested it in various ways and I haven’t found one that works, so if you have any idea how to do that, it would be helpful.

Answer 1

You can resolve this query using the CASE or IF operator Follow an example:

CASE

Create table Test(id integer, title varchar(100), status varchar(100));
insert into Test(id, title, status) values(1, "Hello1", "nok");
insert into Test(id, title, status) values(2, "Hello2", "pen");
insert into Test(id, title, status) values(3, "Hello3", "ok");
insert into Test(id, title, status) values(4, "Hello4", "ok");

select 
    case 
        when status in ('pen', 'ok') then 'confirmado'
        when status in ('nok') then 'rejeitado'
    end statusAgrupado, 
    count(*) 
from 
    Test
group by
    case 
        when status in ('pen', 'ok') then 'confirmado'
        when status in ('nok') then 'rejeitado'
 end;

IF

 create table Test(id integer, title varchar(100), status varchar(100));
 insert into Test(id, title, status) values(1, "Hello1", "nok");
 insert into Test(id, title, status) values(2, "Hello2", "pen");
 insert into Test(id, title, status) values(3, "Hello3", "ok");
 insert into Test(id, title, status) values(4, "Hello4", "ok");

 select 
     if( status in ('pen', 'ok') , 'confirmado', 'rejeitado' ) statusAgrupado,
     count(1) total
 from
     Test
 group by
     if( status in ('pen', 'ok') , 'confirmado', 'rejeitado' );

I tested this code on the website

CASE: https://paiza.io/projects/ZltxTfYL9aliDxRjztpqHg?language=mysql

IF: https://paiza.io/projects/FF9ol7IODRZkvs-3NZP_qg?language=mysql

Answer 2

See if it answers:

SELECT monthname(o.processed_timestamp) AS Mês,
       SUM(CASE o.status = 'confirmado' THEN 1 ELSE 0 END) AS confirmado;
       SUM(o.status ='recusado' OR o.status = 'naoconfirmado' THEN 1 ELSE 0 END) AS recusados
FROM orders_received.partner_commerce_item o
GROUP BY month(o.processed_timestamp),
         o.status;