Select Count(*) FROM with-clause-named table order by a, b not working

countgroup byoracle

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…

…stored procedure…

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.

Thanks.

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…

Update 2:
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.

Best Answer

Unless there is just something specific to Oracle syntax, my suspicion is that you have more than columnA and columnB in your derived table t. If so, you'll need to group by those columns, apply an aggregate to them or do not ask for them (replace t.* with t.columnA, t.columnB)