PL/SQL - how do you get the "first()" row to show and tell it to not count null values?

Question

I am new to PL/SQL but do have experience with Proc SQL (SAS). In the past, whenever I have "counted" a specific variable, it would skip over null values. In PL/SQL, however, I noticed that it still counts them.

example query (counting nulls in pl/sql):

select 
  month, 
  count(et_referral_traffic) as refer, 
  count(et_direct_traffic)   as direct, 
  count(et_organic_search)   as organic, 
  count(et_olavisit)         as visit, 
  count(et_olaimpression)    as olai, 
  count(et_paid_search)      as paid
from
(
  select distinct 
    userid, 
    extract(month from timestamp) as month, 
    et_referral_traffic, 
    et_direct_traffic, 
    et_organic_search, 
    et_olavisit, 
    et_olaimpression, 
    et_paid_search
  from 
    EPIPEN_CLEAN_20150607
)
group by month;

Additionally, I was always able to select a specific variable and then aggregate another variable by selecting First(variable_name), so I would get the first row of that variable_name and order it by whatever other variable I needed to get the right one.

EXAMPLE:

select first_entry, count(touchpointid) as total_entries
from
(
select touchpointid, sessionid, first(revisedentrytype) as first_entry, et_key_action
from epipen_clean_20150607
   group by sessionid, touchpointid
   order by sessionid, touchpointid
)
group by first_entry;

This second query is the one I am most confused about, as I was always able to do this type of query in other forms of SQL. Any help at all as to how I could rewrite this to work in PL SQL would be greatly appreciated!!

Thank you!!!

Answer 1

You do not need PL/SQL - you can do it using SQL using MIN() KEEP ( DENSE_RANK FIRST ... ) [documentation here and another explanation is here]:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE epipen_clean_20150607 ( touchpointid, sessionid, revisedentrytype, time ) AS
          SELECT 1,1,'A', DATE '2015-01-02' FROM DUAL
UNION ALL SELECT 1,1,'B', DATE '2015-01-01' FROM DUAL
UNION ALL SELECT 1,2,'A', DATE '2015-01-01' FROM DUAL
UNION ALL SELECT 1,2,'B', DATE '2015-01-02' FROM DUAL
UNION ALL SELECT 2,1,'A', DATE '2014-12-31' FROM DUAL
UNION ALL SELECT 2,1,'B', DATE '2015-01-01' FROM DUAL
UNION ALL SELECT 2,2,'A', DATE '2015-01-02' FROM DUAL;

Query 1:

select first_entry, count(touchpointid) as total_entries
from
(
  select touchpointid,
         sessionid,
         MIN(revisedentrytype) KEEP ( DENSE_RANK FIRST ORDER BY time ) AS first_entry
  from epipen_clean_20150607
     group by sessionid, touchpointid
     order by sessionid, touchpointid
)
group by first_entry

Results:

| FIRST_ENTRY | TOTAL_ENTRIES |
|-------------|---------------|
|           B |             1 |
|           A |             3 |

Answer 2

SQL count() aggregate function ignores null values passed as argument, check this example.

To get first value you need first_value analytic function, but as in any functions which implies ordering you need to specify how to order a values. You can find example here.

But as follows from your example with group by without specifying any ordering you just need to use a min() aggregate function to get a minimal (or first) value within a group:

select 
  first_entry, 
  count(touchpointid) as total_entries
from
(
  select 
    touchpointid, 
    sessionid, 
    min(revisedentrytype) as first_entry, 
    et_key_action
  from 
    epipen_clean_20150607
   group by sessionid, touchpointid
)
group by first_entry;