Entry

How to use GROUP BY and ORDER BY in the same query?

Jun 16th, 2005 02:29
Paul Kranz, Mark Snowdon, Techek,

I have an existing query with the following structure :
SELECT
  producttype,
  count(projectno) pttypes
FROM
  sal_project
WHERE
  dataset = 'IDL'
GROUP BY
  producttype
ORDER BY
  pttypes
However the query fails telling me that "pttypes" is an invalid
columnname. "pttypes" is NOT an attribute nor a reserved word so I just
can't figure it out.
You need to use
ORDER BY count(projectno)
Why Oracle cant use the synonym you specify, I dont know.
I'd try putting pttypes in quotes: count(projectno) "pttypes" or using 
the "as" clause: count(projectno) as pttypes, or count(projectno) 
as "pttypes"