Search code examples
sqloracle-databasedistinct

Oracle SQL: COUNT DISTINCT BY newly created variable

I want to count the distinct entries of ID by the newly created column MONTH_YEAR in order to get a table which stores the distinct values MONTH_YEAR (e.g. 07-2020) and the corresponding distinct count of ID (e.g. 2345). I used this code which does not work out:

SELECT TIME, to_char(TIME, 'MM-YYYY') "MONTH_YEAR", COUNT (DISTINCT ID) "IdCount"
GROUPBY MONTH_YEAR
FROM table

I think I have to create a new table with only the two mentioned values, haven't I? If that is true, how should I amend my code?

Solution

  • An alias cannot be used within the GROUP BY clause, and replace the order of FROM and GROUP BY clauses such as

    SELECT TO_CHAR(time, 'MM-YYYY') AS month_year, COUNT (DISTINCT id) AS "IdCount"    
  FROM tab
 GROUP BY TO_CHAR(time, 'MM-YYYY')

    btw, all non-aggregated columns should be included in the GROUP BY list.