Column displays each count

Question

I am trying to run a query to count records based on a time stamp year to year. Here is a query that I currently have:

SELECT plocation AS GROUP_NUMBER, count(DISTINCT UMEMBERID) AS YEAR_ONE
    FROM HEALTH_ASSESSMENT
    WHERE PUBLICATIONDT > '31-DEC-13'AND PUBLICATIONDT < '01-JAN-15'
    GROUP BY PLOCATION

It counts exactly what I need for the time frame and displays the following:

GROUP_NUMBER | YEAR_ONE

G12345       |      141
G12346       |       61

Where YEAR_ONE is the dates I've listed, which is essentially 2014. Is there a way to create an additional column that counts a second time frame? For example, something like this:

GROUP_NUMBER |   YEAR_ONE     | YEAR_TWO 

G12345       |       141      |     92    
G12346       |       61       |     57
G12444       |  NULL OR BLANK |     16

Here G12444 may not have any records in the YEAR_ONE parameters, but would have records in the YEAR TWO parameters. Sometimes this query would be on a month to month basis so having each column be setup with the parameters of 01-JAN-15 - 31-JAN-15 may also be needed as well.

How can I accomplish this?

Answer 1

You should be able to do this with a CASE statement. Something like this should work:

SELECT plocation AS GROUP_NUMBER
    , count(DISTINCT CASE WHEN EXTRACT( YEAR from PUBLICATIONDT) = 2014 THEN UMEMBERID ELSE null END) AS YEAR_ONE
    , count(DISTINCT CASE WHEN EXTRACT( YEAR from PUBLICATIONDT) = 2015 THEN UMEMBERID ELSE null END) AS YEAR_two
    FROM HEALTH_ASSESSMENT
    WHERE EXTRACT( YEAR from PUBLICATIONDT) BETWEEN 2014 and 2015       
GROUP BY PLOCATION