Counting and grouping NULL and non NULL values with count results in separate columns-CodePudding

Really stumped on this one. I'm trying to figure out how i can get my SQL query shown below to do a count of null and not null aircraft ID's with a table of results that has two count columns, one for NULL aircraft IDs and another for Not NULL aircraft IDs and is grouped by operator, so it looks something like this:

SELECT DISTINCT org.organization                 "operator",
                ah.aircraft_registration_country "country",
                ah.aircraft_registration_region  "region",
                acl.aircraft_master_series       "aircraft type",
                ah.publish_date                  "publish date",
                f.aircraft_id                    "aircraft_id"

FROM   ((((("flights"."tracked_utilization" f
            left join "pond_dataops_analysis"."latest_aircraft" a
                   ON ( a.aircraft_id = f.aircraft_id ))
           left join fleets.aircraft_all_history_latest ah
                  ON ( ( ( ah.aircraft_id = f.aircraft_id )
                         AND ( Coalesce(f.actual_runway_departure_time_local,
                               actual_gate_departure_time_local,
                                     published_gate_departure_time_local) >=
                               ah.start_event_date ) )
                       AND ( Coalesce(f.actual_runway_departure_time_local,
                             actual_gate_departure_time_local,
                                   published_gate_departure_time_local) <
                           ah.end_event_date ) ))
          left join fleets.organizations_latest org
                 ON ( org.organization_id = ah.operator_organization_id )))
        left join fleets.aircraft_usage_history_latest ash
               ON ( ( ( ( ash.aircraft_id = f.aircraft_id )
                        AND ( start_event_date >= ash.usage_start_date ) )
                      AND ( start_event_date < ash.usage_end_date ) )
                    AND ( aircraft_usage_classification = 'Primary' ) )
        left join fleets.aircraft_configuration_history_latest accl
               ON ash.aircraft_id = accl.aircraft_id
        left join fleets.aircraft_configurations_latest acl
               ON accl.aircraft_configuration_id = acl.aircraft_configuration_id
       )
WHERE  (((( f.flight_departure_date > ( "Now"() - interval '90' day ) ))))

Not sure how to do a 'count/group by' so that the query can show what i'm after.

Regards, Mark

CodePudding user response：

Something like this:

select
    x, y, z,
    sum( case when aircraft_id is null then 1 else 0 end ) as null_cnt,
    sum( case when aircraft_id is null then 0 else 1 end ) as notnull_cnt
from
    (inline subquery)
group by
    x, y, z

FWIW, you don't need all those parentheses in your query, they are unnecessary and more confusing than helpful. They do have their place in some cases, especially when dealing with "OR" conditions, but for this query they are completely superfluous:

FROM
    "flights"."tracked_utilization" f
    left join "pond_dataops_analysis"."latest_aircraft" a
        ON a.aircraft_id = f.aircraft_id
    left join fleets.aircraft_all_history_latest ah
        ON ah.aircraft_id = f.aircraft_id
        AND Coalesce(f.actual_runway_departure_time_local, actual_gate_departure_time_local, published_gate_departure_time_local) >= ah.start_event_date
        AND Coalesce(f.actual_runway_departure_time_local, actual_gate_departure_time_local, published_gate_departure_time_local) < ah.end_event_date
    left join fleets.organizations_latest org
        ON org.organization_id = ah.operator_organization_id
    left join fleets.aircraft_usage_history_latest ash
        ON ash.aircraft_id = f.aircraft_id
        AND start_event_date >= ash.usage_start_date
        AND start_event_date < ash.usage_end_date
        AND aircraft_usage_classification = 'Primary'
    left join fleets.aircraft_configuration_history_latest accl
        ON ash.aircraft_id = accl.aircraft_id
    left join fleets.aircraft_configurations_latest acl
        ON accl.aircraft_configuration_id = acl.aircraft_configuration_id
WHERE
    f.flight_departure_date > "Now"() - interval '90' day