CODEWITHSUNDEEP
sqlpostgresql
Jenny
Jenny

Reputation: 76

SQL group by and erase rows

I have a table looks like this 

company_name | feature_name | feature_value
 abc         |   income     |  315
 abc         |   cash       |  213
 abc         |   val        |  9
 goo         |   income     |  123
 goo         |   cash       |  487
 goo         |   val        |  990 
 sdf         |   income     |  null value
 sdf         |   cash       |  null value
 sdf         |    val       |  null value

Here is the script I used

select company_name, 
         max(case when feature_name='income' then feature_value end) as income_value,
         max(case when feature_name='val' then feature_value end) as val_value
   from a_table
   group by company_name

I got 

company_name | income_value | val_value
 abc         |   315        |  9
 goo         |   123        |  990
 sdf         |   null       |  null

But what I want is only to get rows where not all values that I'm interested in (income_value, val_value) are null. Like

company_name | income_value | val_value
 abc         |   315        |  9
 goo         |   123        |  990

Except for doing select twice to get rid of the value null rows, is there any faster way to deal with it? Thanks!

Upvotes: 0

Views: 60

Answers (3)

Belayer
Belayer

Reputation: 14934

You don't need to repeat case filters with HAVING, just add the "is not null: in a WHERE clause.

with  a_table(company_name, feature_name, feature_value) as
 (  values ('abc', 'income', 315)
        , ('abc', 'cash',   213)
        , ('abc', 'val',    9)
        , ('goo', 'income', 123)
        , ('goo', 'cash',   487)
        , ('goo', 'val',    990)
        , ('sdf', 'income', null)
        , ('sdf', 'cash',   null)
        , ('sdf', 'val',    null)
 )
select company_name 
     , max(case when feature_name='income' then feature_value end) as income_value
     , max(case when feature_name='val' then feature_value end) as val_value
  from a_table
 where feature_value is not null
 group by company_name;

It also eliminates the need to process the through the case predicates.

Upvotes: 0

Kaminda Berugoda
Kaminda Berugoda

Reputation: 38

You can remove the nulls in the HAVING CLAUSE like

    SELECT
      company_name,
      MAX(CASE WHEN feature_name = 'income' THEN feature_value end) as income,
      MAX(CASE WHEN feature_name = 'val' THEN feature_value end) as val
    FROM
      table1
    GROUP BY company_name
    HAVING
    MAX(CASE WHEN feature_name = 'income' THEN feature_value end) is not null 
    AND
    MAX(CASE WHEN feature_name = 'val' THEN feature_value end) is not null;

Upvotes: 0

forpas
forpas

Reputation: 164174

Add a HAVING clause with the condition you want:

SELECT
  company_name,
  MAX(CASE WHEN feature_name = 'income' THEN feature_value END) AS income,
  MAX(CASE WHEN feature_name = 'val' THEN feature_value END) AS val
FROM tablename
GROUP BY company_name
HAVING 
  MAX(CASE WHEN feature_name = 'income' THEN feature_value END) IS NOT NULL
  OR
  MAX(CASE WHEN feature_name = 'val' THEN feature_value END) IS NOT NULL

See the demo.
Results:

| company_name | income | val |
| ------------ | ------ | --- |
| abc          | 315    | 9   |
| goo          | 123    | 990 |

Upvotes: 1

Related Questions