I have a series of tables defined in a stored procedure to eventually return to the application like this, then culminating to a simple table which contains distinct values from a previously defined table and the count of how many times that distinct value repeats. For some reason it does not seem to be working...
OPEN p_retcursor FOR WITH r AS (SELECT ... FROM MYDATATABLE), s AS (SELECT ... FROM r WHERE ...) , t AS (SELECT ... WHERE ... JOIN ... ON ... FROM s) SELECT t.*, COUNT(*) as distinctABcount FROM t GROUP BY t.columnA, t.columnB
How do I get this to work? I guess technically columnA and columnB are not columns of a table, but are columns of t so I think the order of evaluation is a bit messed up due to the fact that t is not an actual table... Query fails around the count(*) column part and may be failing on the GROUP BY.
Update 1: I have a theory that if I string together all the named tables into subqueries inside the from clause it will work... but that does not seem like a solution which will produce readable code considering this stored procedure is hundreds of lines long...
The theory was wrong, the derived table was not the problem, but as pointed out by the answer, you cannot ask for more columns than what you have set in the group by clause. I fixed it by correcting the final section of the query like suggested:
select t.columnA, t.columnB, COUNT(*) as distinctABcount FROM t GROUP BY t.columnA, t.columnB
which runs correctly.