CODEWITHSUNDEEP
mysqlsql
Juraj Béger
Juraj Béger

Reputation: 135

Group more records based on values

I have the following table:

contract_nr     id_supplier id_seller   id_vendor
01              1521        NULL        NULL
01              1521        2           NULL
01              NULL        NULL        538
02              NULL        NULL        154
02              NULL        NULL        159
03              2626        17          NULL
03              NULL        17          124

The result should be a grouped table with the category field based on these rules:

  1. IF there is a id_supplier or id_seller other then NULL, then it is category1
  2. IF there is only an id_vendor and id_supplier and id_seller is NULL, then it is category2

The result should look like this: 

contract_nr     category
01              category1
02              category2
03              category1

Upvotes: 1

Views: 58

Answers (3)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

You can use conditional aggregation:

SELECT contract_nr,
       CASE 
          WHEN SUM(id_supplier IS NOT NULL) + SUM(id_seller IS NOT NULL) >= 1 
             THEN 'category1'
          WHEN SUM(id_vendor IS NOT NULL) THEN 'category2'
          ELSE 'N/A'
       END   
FROM mytable
GROUP BY contract_nr;

In MySQL you can use SUM together with a boolean expression in order to find how many times a condition is satisfied within each group. Hence, using the predicate:

SUM(id_supplier IS NOT NULL) + SUM(id_seller IS NOT NULL) >= 1

we can test whether, for a given contract_nr, at least one record exists having either id_supplier, or id_seller, or both not null.

Demo here

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

You can use aggregation paired with a case expression to check your required condition on the data:

select
    contract_nr,
    case
        when max(id_supplier) is not null 
                or max(id_seller) is not null
            then 'category1'
        when max(id_vendor) is not null
                and max(id_supplier) is null 
                and max(id_seller) is null
            then 'category2'
    end as category
from your_table
group by contract_nr;

It uses the fact the max returns maximum non null value in the aggregation group. If there are only nulls present in the group, then it will return a null.

Also note that it will return null in category if both the conditions do not meet. You may want to add an else 'category3' to the case to capture that.

Demo

Upvotes: 1

Blank
Blank

Reputation: 12378

Group by contract_nr then use case when and count(which excludes null value) to do this:

select
    contract_nr,
    case when count(id_supplier) = 0 and count(id_seller) = 0 and count(id_vendor) > 0 then 'category2'
         else 'category1'
    end as category
from yourtable
group by contract_nr

See demo in sqlfiddle.

Upvotes: 0

Related Questions