How to not show rows that have null values in all the columns-CodePudding

SELECT DISTINCT CC1.DATE,
CC2.SL_TLF_CACT_ZS,
CC3.SL_TLF_TOTL_IN,
CC4.SL_UEM_PRIM_ZS,
CC5.SL_UEM_TERT_ZS,
CC6.SL_UEM_TOTL_ZS

FROM country_data CC1
FULL JOIN
    (SELECT date, value AS SL_TLF_CACT_ZS
        from country_data
        where indicator_code='SL_TLF_CACT_ZS'
        and country_code='USA') CC2 ON CC1.DATE=CC2.DATE
    
FULL JOIN
    (SELECT date, value AS SL_TLF_TOTL_IN
        from country_data
        where indicator_code='SL_TLF_TOTL_IN'
        and country_code='USA') CC3 ON CC1.DATE=CC3.DATE
    
FULL JOIN
    (SELECT date, value AS SL_UEM_PRIM_ZS
        from country_data
        where indicator_code='SL_UEM_PRIM_ZS'
        and country_code='USA') CC4 ON CC1.DATE=CC4.DATE


 Left outer joinFULL JOIN
    (SELECT date, value AS SL_UEM_TERT_ZS
        from country_data
        where indicator_code='SL_UEM_TERT_ZS'
        and country_code='USA') CC5 ON CC1.DATE=CC5.DATE
    
FULL JOIN
    (SELECT date, value AS SL_UEM_TOTL_ZS
        from country_data
        where indicator_code='SL_UEM_TERT_ZS'
        and country_code='USA') CC6 ON CC1.DATE=CC6.DATE
    
ORDER BY date;

enter image description here

Hi, I have this query where the 1st few rows have null values. I want to exclude them from my results using a where clause but when i do a where clause with a Not NULL condition, it does not work. How would I do this? I am using Postgres SQL

CodePudding user response：

Use the concat() function to combine each column (not pretty but it works). If the result in null then each column in null.

select ... 
where concat(CC2.SL_TLF_CACT_ZS,
             CC3.SL_TLF_TOTL_IN,
             CC4.SL_UEM_PRIM_ZS,
             CC5.SL_UEM_TERT_ZS,
             CC6.SL_UEM_TOTL_ZS)
       is not null;

Do Not use the the concatenation operator (||) as it handles nulls differently. If any column is null the result in null.

CodePudding user response：

You can remove all the derived tables and do the pivot/crosstab using filtered aggregation. Then you can limit the result to those that contain at least one value:

SELECT "date",
       max(value) filter (where indicator_code = 'SL_TLF_CACT_ZS') as SL_TLF_CACT_ZS,
       max(value) filter (where indicator_code = 'SL_TLF_TOTL_IN') as SL_TLF_TOTL_IN,
       max(value) filter (where indicator_code = 'SL_UEM_PRIM_ZS') as SL_UEM_PRIM_ZS,
       max(value) filter (where indicator_code = 'SL_UEM_TERT_ZS') as SL_UEM_TERT_ZS,
       max(value) filter (where indicator_code = 'SL_UEM_TOTL_ZS') as SL_UEM_TOTL_ZS
FROM country_data 
where indicator_code IN ('SL_TLF_CACT_ZS', 'SL_TLF_TOTL_IN', 'SL_UEM_PRIM_ZS', 'SL_UEM_TERT_ZS', 'SL_UEM_TOTL_ZS')
  and country_code='USA'
group by "date"
having count(value) > 0;