I have a list of names in capitals and initials. i have removed the initails and removed the uppercase but distinct still shows them seperate (and it would be nice to get rid of the s of the end of the names.

SQL Oracle

SELECT DISTINCT (upper
     ( REGEXP_SUBSTR(NAME, '(\S )$'))) as NAMEZ,
    count(case when daysout is NULL then 1 else null end) as home,
    count(case when daysout is NOT NULL then 1 else null end) as away,
    count(*) as daystotal
    FROM peoplein

so what i get from this is

johns
johns
john
Jack
jack

before the reg and upper I used to get

Johns
JOHNS
John
Jack
JACK

I would like to just get them to be

john
jack

thank you for your help

CodePudding user response：

DISTINCT is not a function; it is a keyword that applies to all columns. In this case you want a GROUP BY clause instead of using DISTINCT:

SELECT UPPER( REGEXP_SUBSTR(NAME, '(\s|^)(\S ?)s?$', 1, 1, NULL, 2)) as name,
       COUNT(case when daysout is NULL then 1 else null end) as home,
       COUNT(case when daysout is NOT NULL then 1 else null end) as away,
       COUNT(*) as daystotal
FROM   peoplein
GROUP BY
       UPPER( REGEXP_SUBSTR(NAME, '(\s|^)(\S ?)s?$', 1, 1, NULL, 2))

or, using simple (faster) string functions:

SELECT TRIM(
         TRAILING 'S' FROM
         UPPER( SUBSTR(NAME, INSTR(NAME, ' ', -1)   1) )
       ) as name,
       COUNT(case when daysout is NULL then 1 else null end) as home,
       COUNT(case when daysout is NOT NULL then 1 else null end) as away,
       COUNT(*) as daystotal
FROM   peoplein
GROUP BY
       TRIM(
         TRAILING 'S' FROM
         UPPER( SUBSTR(NAME, INSTR(NAME, ' ', -1)   1) )
       )

(Although this latter query would trim multiple S characters from the end; however, that is fixable.)

Which, for the sample data:

CREATE TABLE peoplein(name, daysout) AS
  SELECT 'Abbot Alice', NULL FROM DUAL CONNECT BY LEVEL <= 3 UNION ALL
  SELECT 'Abbot Alice', 1    FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
  SELECT 'Baron Betty', 1    FROM DUAL CONNECT BY LEVEL <= 4 UNION ALL
  SELECT 'Count Chris', NULL FROM DUAL CONNECT BY LEVEL <= 5 UNION ALL
  SELECT 'Count Chris', 1    FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
  SELECT 'Doris',       1    FROM DUAL CONNECT BY LEVEL <= 6;

Both output:

Note: Are you sure you want to remove a trailing S from a person's name?

fiddle