asked on

How to add a sequential row number column to an aggregate view.

I have a query that agrregates the values from a table in oracle. I want to add a column with sequential rownumbers and I get the following error:

"Ora-00979 Not a valid Group by expression."

It is trying to pull the row numbers from the source data. What I want is to have a squential id number colum.

SELECT ROWNUM,
COUNT (DISTINCT COVERPAGE_GIS.PARENT_COMPANY) COMPANIES,
FORM477_PART_VI_GIS.TRACT_FIPS
FROM COVERPAGE_GIS, FORM477_PART_VI_GIS
WHERE (COVERPAGE_GIS.CONTROL_ID = FORM477_PART_VI_GIS.CONTROL_ID)
AND ( (FORM477_PART_VI_GIS.UPLOAD_RATE_CODE >= 2)
OR (FORM477_PART_VI_GIS.DOWNLOAD_RATE_CODE >= 2))
GROUP BY FORM477_PART_VI_GIS.TRACT_FIPS

Raja Jegan R

Try this:

SELECT ROWNUM,
         COUNT (DISTINCT COVERPAGE_GIS.PARENT_COMPANY) OVER ( partition by  FORM477_PART_VI_GIS.TRACT_FIPS) COMPANIES,
         FORM477_PART_VI_GIS.TRACT_FIPS
    FROM COVERPAGE_GIS, FORM477_PART_VI_GIS
   WHERE (COVERPAGE_GIS.CONTROL_ID = FORM477_PART_VI_GIS.CONTROL_ID)
         AND ( (FORM477_PART_VI_GIS.UPLOAD_RATE_CODE >= 2)
              OR (FORM477_PART_VI_GIS.DOWNLOAD_RATE_CODE >= 2))

Open in new window

ASKER CERTIFIED SOLUTION

k_murli_krishna

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

OR1

ASKER

Your solution works with or without the iq. Can you tell me the reason for it?

Thanks

k_murli_krishna

In Oracle iq is optional. In DB2, iq or AS iq is compulsory for nested query (overall/select list/where/having) i.e. normal OR correlated.