Rewrite query with COUNT(*) and GROUP BY as Cartesian Join

Question

I would like to ask whether it is possible to rewrite the following query:

SELECT TEAM, COUNT(*)
FROM
(
    SELECT    ID, TEAM
    FROM      MY_TABLE
    WHERE     TO_CHAR(A.SUBMIT_DATE, 'YYYY') = '2019' 
    GROUP BY  ID, TEAM
)
GROUP BY TEAM

using the cartesian join style? The reason is that I'm using Peoplesoft and it does not allow such subquery without creating another view for it.

Answer 1

Your inner query is flattening out the table to get distinct ID and TEAM combinations for 2019, which are then counted by the outer query. You can shortcut it to a simple COUNT(DISTINCT) query:

SELECT TEAM, COUNT(DISTINCT ID)
FROM MY_TABLE
WHERE TO_CHAR(SUBMIT_DATE, 'YYYY') = '2019'
GROUP BY TEAM;

NOTEs:

• As Gary Myers points out in the comments, this won't work if there are null ID values. If ID is nullable, see the next bullet point.

• Brilliant hack from Tejash in the notes below, to use if ID can be NULL:

  SELECT TEAM, COUNT(DISTINCT ID || TEAM)
  ... and then the rest of the query from above
  

Here's a Fiddle with your query and mine. Tejash also did a fiddle.

One more thing. If you have a lot of records (thousands or more), and if you have an index on SUBMIT_DATE, you can optimize the query by not using a function on SUBMIT_DATE:

SELECT TEAM, COUNT(DISTINCT ID)
FROM MY_TABLE
WHERE SUBMIT_DATE >= DATE '2019-01-01'
  AND SUBMIT_DATE <  DATE '2020-01-01'
GROUP BY TEAM;

If it's a big table with lots of rows and lots of years, the optimized version will be noticeably faster.