How to cast DATE column to get proper aggregation in SQL ORACLE

Question

I have previously posted in: Aggregating based on grouping of multiple columns. After two weeks of trying subqueries and analytical functions to do a basic COUNT and GROUP BY of 7 columns, I think that I discovered my source of error was that the DATE field. (Through trial and error of all of the other columns, the COUNT properly aggregates to 1 count row grouped by the 6 columns.)

Now, the question remains: is there something that I can do to ensure that I am only bringing back distinct dates for the format DD MMM YY (without times)? Based on this post, it seems like perhaps the issue is that I am bringing back times with the date even though ORACLE only shows the date portion? Date column in oracle. Any pointers on CASTING the DATE_STARTED value so that I can use the initial WHERE clause to filter my initial search and then later use DATE like a string to make a UUID?

My code so far (with some code removed e.g., … = CASE STATEMENT to create an additional column, ANALYSIS_TYPE_ALIAS ):

WITH TALLY as (
SELECT PERSON.NAME, PERSON.PHASE, TEST.DATE_STARTED, TEST.ANALYSIS, SPECIMEN.GROUP, TEST.STATUS,
 ANALYSIS.ANALYSIS_TYPE...
    FROM DB.TEST
    INNER JOIN
    DB.SAMPLE ON
    TEST.SPECIMEN_NUMBER = SPECIMEN.SPECIMEN_NUMBER
    INNER JOIN 
    DB.PRODUCT ON
    SPECIMEN.PERSON = PERSON.NAME
    INNER JOIN
    DB.ANALYSIS ON
    TEST.ANALYSIS = ANALYSIS.NAME
    WHERE PERSON.NAME = 'Joe'
    AND TEST.DATE_STARTED >= '20-DEC-18' AND TEST.DATE_STARTED <='03-APR-19'
    AND PERSON.PHASE = 'PHASE1'
    ORDER BY TEST.DATE_STARTED),

SUMMARY_COMBO AS (SELECT DISTINCT(TALLY.DATE_STARTED), CONCAT(CONCAT(CONCAT(CONCAT(DATE_STARTED,ANALYSIS),STATUS), GROUP), ANALYSIS_TYPE_ALIAS) AS UUID,
TALLY.NAME, TALLY.PHASE,TALLY.ANALYSIS,TALLY.GROUP, TALLY.STATUS, ANALYSIS_TYPE_ALIAS
FROM TALLY)

SELECT SUMMARY_COMBO.NAME,SUMMARY_COMBO.PHASE,SUMMARY_COMBO.ANALYSIS, SUMMARY_COMBO.GROUP,SUMMARY_COMBO.STATUS,SUMMARY_COMBO.ANALYSIS_TYPE_ALIAS,SUMMARY_COMBO.UUID,
COUNT(SUMMARY_COMBO.UUID) AS S_COUNT
FROM SUMMARY_COMBO
GROUP BY SUMMARY_COMBO.NAME,SUMMARY_COMBO.PHASE,SUMMARY_COMBO.ANALYSIS,SUMMARY_COMBO.STATUS,SUMMARY_COMBO.ANALYSIS_TYPE_ALIAS,SUMMARY_COMBO.UUID
HAVING COUNT(*) >=1
ORDER BY UUID;

This yields something like this:UUID produces COUNT for each date

Ideally, I could also include the DATE_STARTED as well. When I currently try to include DATE_STARTED, the aggregation does not work, as the S_COUNT is 1 for each permutation.

Any advice is greatly appreciated.

If you could provide sample data to replace your `TALLY` Common Table Expression and a sample of how you are expecting the results, that would make helping you much easier. — EJ Egyed, Sep 18 '20 at 20:28
The syntax for date literals is `date '2018-12-20'`, not `'20-DEC-18'`. See [Oracle SQL Language Reference: Literals](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Literals.html#GUID-8F4B3F82-8821-4071-84D6-FBBA21C05AC1). — William Robertson, Sep 19 '20 at 12:00

score 1 · Answer 1 · answered Sep 18 '20 at 20:33

perhaps the issue is that I am bringing back times with the date even though ORACLE only shows the date portion?

Very likely, yes. Oracle's DATE datatype stores the date and time, which might be misleading as opposed to databases that have separate date and datetime datatypes. You can change the default string format of dates to confirm that:

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

Then rerun the query and see the results you get.

A simple solution to remove the time portion is trunc():

trunc(test.date_started)

score 1 · Answer 2 · answered Sep 18 '20 at 20:51

I believe the query below should give you what you need. Using TRUNC on a DATE column in Oracle strips the time from the date. You can then use that in your GROUP BY to get the number of tests that were done on each day.

I also changed your date parameters to use the TO_DATE function. It is bad practice to use a 2 digit year. It is also a good idea to case your parameters to dates when they are going to be used against a DATE column.

  SELECT PERSON.NAME,
         PERSON.PHASE,
         TEST.ANALYSIS,
         SPECIMEN.GROUPr,
         TEST.STATUS,
         ANALYSIS.ANALYSIS_TYPE_ALIAS,
         TO_CHAR (TRUNC (TEST.DATE_STARTED), 'DD-MON-YYYY')
             AS date_started,
         TO_CHAR (TRUNC (TEST.DATE_STARTED), 'DD-MON-YYYY') || TEST.ANALYSIS || TEST.STATUS || ANALYSIS.ANALYSIS_TYPE_ALIAS
             AS uuid,
         COUNT (*)
             AS s_count
    FROM DB.TEST
         INNER JOIN DB.SAMPLE ON TEST.SPECIMEN_NUMBER = SPECIMEN.SPECIMEN_NUMBER
         INNER JOIN DB.PRODUCT ON SPECIMEN.PERSON = PERSON.NAME
         INNER JOIN DB.ANALYSIS ON TEST.ANALYSIS = ANALYSIS.NAME
   WHERE     PERSON.NAME = 'Joe'
         AND TEST.DATE_STARTED >= TO_DATE ('20-DEC-2018')
         AND TEST.DATE_STARTED <= TO_DATE ('03-APR-2019')
         AND PERSON.PHASE = 'PHASE1'
GROUP BY PERSON.NAME,
         PERSON.PHASE,
         TEST.ANALYSIS,
         SPECIMEN.GROUPr,
         TEST.STATUS,
         ANALYSIS.ANALYSIS_TYPE_ALIAS,
         TRUNC (TEST.DATE_STARTED)
ORDER BY TRUNC (TEST.DATE_STARTED),
         TEST.ANALYSIS,
         TEST.STATUS,
         ANALYSIS.ANALYSIS_TYPE_ALIAS;

Don't use implicit date conversions (i.e. `TO_DATE('20-DEC-2018')`); instead, you should either include an explicit format model `TO_DATE('20-DEC-2018','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')` or use a date literal `DATE '2018-12-20'`. — MT0, Sep 18 '20 at 21:21

score 0 · Answer 3 · answered Sep 18 '20 at 20:26

0

You can explicitly convert DATE_STARTED to a varchar using the format you prefer:

TO_CHAR(test.date_started, 'DD MON YYYY')

Using this expression instead of all the instances of TEST.DATE_STARTED you have in your query should fix the issue.

answered Sep 18 '20 at 20:26

Mureinik

297,002
52
306
350

How to cast DATE column to get proper aggregation in SQL ORACLE

3 Answers3