CODEWITHSUNDEEP
mysqlsqlcountmultiple-columns
user518206
user518206

Reputation: 93

Counting distinct dates from two columns in a single SQL table

I've been reading through the solutions of similar problems posted here, but none seem to resolve my particular issue.

I currently have a table (CT_JOINED) that includes three columns: an identifer column (TUMOURID), a date column (AVCT_DATE) and another date column (OPDATE).

As an example, the columns for two IDs look as follows:

ID, AVCT_DATE, OPDATE
1, 06-APR-13, 06-APR-13
1, 06-APR-13, 14-JUN-13
1, 06-APR-13, 22-JUN-13
2, 03-APR-14, 10-DEC-15
2, 03-APR-14, 31-DEC-15

What I'm attempting to do is create a column that is equal to the number of unique dates per ID. So the result for ID 1 would be 3 and the result for ID 2 would be 3.

I have attempted a count of distinct values across the two columns, but this does not provide the answers above (it instead reports values of 3 and 2 respectively):

select TUMOURID, COUNT(DISTINCT(AVCT_DATE || OPDATE)) AS COUNT 
FROM CT_JOINED
GROUP BY TUMOURID;

The same thing happens if I try and do the same in a new table: 

CREATE TABLE CT_DISTINCT AS (
SELECT TUMOURID, COUNT(*) AS COUNT
FROM (
SELECT DISTINCT TUMOURID, AVCT_DATE, OPDATE
FROM CT_JOINED)
GROUP BY TUMOURID
);

I'm at a loss. Is it possible?

Upvotes: 1

Views: 1244

Answers (4)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use UNION to avoid duplicate date & just use count(*) :

SELECT tumourid, COUNT(date)
FROM ((SELECT tumourid, avct_date AS date
       FROM ct_joined
      ) UNION
      (SELECT tumourid, opdate 
       FROM ct_joined
      )
     ) t
GROUP BY tumourid;

Upvotes: 1

user518206
user518206

Reputation: 93

All of the answers below work like a charm with a few tweaks to also account for rows with null values. For instance:

SELECT TUMOURID, COUNT(*)
FROM ((SELECT TUMOURID, AVCT_DATE AS DTE
       FROM CT_JOINED
       WHERE AVCT_DATE IS NOT NULL
      ) UNION
      (SELECT TUMOURID, OPDATE AS DTE
       FROM CT_JOINED
       WHERE OPDATE IS NOT NULL
      )
     ) T
GROUP BY TUMOURID;

Many thanks.

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

You could use:

SELECT TUMOURID, COUNT(DISTINCT d) AS cnt
FROM (select TUMOURID, AVCT_DATE AS d
      FROM CT_JOINED
      UNION ALL
      SELECT TUMOURID, OPDATE AS d) sub
GROUP BY TUMOURID;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Unpivot the data and then use count(distinct) or remove duplicates along the way:

select tumourid, count(*)
from ((select tumourid, avct_date as dte
       from ct_joined
      ) union -- intentional to remove duplicates
      (select tumourid, opdate as dte
       from ct_joined
      )
     ) t
group by tumourid;

Upvotes: 1

Related Questions