DB2 Querying a table for both DISTINCT and COUNT(DISTINCT)-CodePudding

I'm trying to query distinct Update_Date but also return a column that counts the total number of distinct Update_Date

The table right now (Only Update_Date is being queried):

Update_Date  |  Status
-------------------------------
2022-03-01   |  1
2022-03-02   |  2
2022-03-03   |  1
2022-03-03   |  2
2022-03-03   |  3

My SQL right now:

SELECT 
DISTINCT Update_Date, 
COUNT (DISTINCT Update_Date) AS Update_Date_CNT
FROM TABLE

The expected result should be

Update_Date  |  Update_Date_CNT
-------------------------------
2022-03-01   |  3
2022-03-02   |  3
2022-03-03   |  3

Right now I'm getting an error:

DB2 Database Error: ERROR [42803] [IBM][DB2/AIX64] SQL0119N An expression starting with "CLC_YYMM" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified. SQLSTATE=42803

If I run the SELECT with only one of the two columns, no errors are thrown.

SELECT 
DISTINCT Update_Date, 
FROM TABLE

Update_Date  
-----------
2022-03-01   
2022-03-02   
2022-03-03

SELECT 
COUNT (DISTINCT Update_Date) AS Update_Date_CNT
FROM TABLE

Update_Date_CNT 
---------------
3

I'm guessing it's because the Update_Date_CNT column returns 1 row but the Update_Date column returns 3 rows, and the syntax so far doesn't say to "repeat" the '3' returned by Update_Date_CNT for each Update_Date row? I read in another post about joining but the error message mentions GROUP BY - how do I fix this error?

CodePudding user response：

Disclaimer, I don't use DB2 so there may be a better method, but either using a CTE or a derived query seems to work with DB2 11.1

Option #1

WITH cte AS (
  SELECT COUNT(DISTINCT Update_Date) AS Update_Date_CNT
  FROM YourTable
)
SELECT DISTINCT t.Update_Date, cte.Update_Date_CNT
FROM   YourTable t CROSS JOIN cte

Option #2

SELECT DISTINCT t.Update_Date, totals.Update_Date_CNT
FROM   YourTable t CROSS JOIN 
       (
         SELECT COUNT(DISTINCT Update_Date) AS Update_Date_CNT
         FROM YourTable
       ) totals

Results:

UPDATE_DATE	UPDATE_DATE_CNT
2022-03-01	3
2022-03-02	3
2022-03-03	3

CodePudding user response：

You may use COUNT() here as an analytic function:

SELECT DISTINCT Update_Date, COUNT(*) OVER () AS Update_Date_CNT
FROM YOUR_TABLE;

CodePudding user response：

Use cross join as follows

SELECT *
FROM (
    SELECT DISTINCT Update_Date
    FROM TABLE
) T1
, 
-- cross join /*new version*/
(
    SELECT COUNT (DISTINCT Update_Date) AS Update_Date_CNT
    FROM TABLE
) T2

SELECT 
  DISTINCT update_date, 
  (
    SELECT 
      Count (DISTINCT update_date) AS Update_Date_CNT 
    FROM 
      TABLE
  ) Update_Date_CNT 
FROM 
  TABLE