Calculate Count Distinct without grouped columns in Oracle

aggregategroup byoracle

I am trying to extract a few records and I ran into the "not a single group" error. This error can be resolved by removing the (count(distinct dc.DUCT_FID)) as NO_OF_WAYS column expression. However, I really need this column in my extraction.

SELECT TELEM.EXCH_CODE, 
       TELEM.CENTRAL_OFFICE, 
       TELEM.IPID as SEGMENT_IPID, 
       TELEM.FEATURE_STATE AS SEGMENT_STATUS,
       DCON.G3E_FID AS DUCT_FID, 
       DELEM.FEATURE_STATE AS DUCT_STATUS,
       COND.FR_MH, 
       COND.TO_MH, 
       TI.TRENCH_LENGTH, 
       (count(distinct dc.DUCT_FID)) as NO_OF_WAYS,
       TELEM.YEAR_PLACED AS YEAR_BUILD,
       ADDR.STREET_NAME AS LOCATION, 
       ADDR.POSTAL_CODE, 
       TELEM.OWNERSHIP
FROM SGTEL10.B$GC_NETELEM_0404 TELEM
JOIN SGTEL10.B$GC_COND_0404 COND ON COND.G3E_FID = TELEM.G3E_FID
JOIN SGTEL10.TRENCH_INFO TI ON TI.PIPE_FID = COND.G3E_FID 
LEFT JOIN SGTEL10.B$GC_CONTAIN_0404 FCON ON FCON.G3E_OWNERFID = TELEM.G3E_FID
LEFT JOIN SGTEL10.B$GC_CONTAIN_0404 DCON ON DCON.G3E_OWNERFID = FCON.G3E_OWNERFID
LEFT JOIN SGTEL10.B$GC_DUCT_0404 DUCT ON DUCT.G3E_FID = DCON.G3E_FID
LEFT JOIN SGTEL10.DUCT_COUNT DC ON DC.DUCT_FID = DUCT.G3E_FID
LEFT JOIN SGTEL10.B$GC_NETELEM_0404 DELEM ON DELEM.G3E_FID = DUCT.G3E_FID
LEFT JOIN SGTEL10.B$GC_ADDRESS_0404 ADDR ON ADDR.G3E_FID = COND.G3E_FID
WHERE TELEM.IPID IN (4001702,
                     4005753,
                     4005757,
                     4005785
                    );

What should I do?

Best Answer

You use COUNT(DISTINCT ..) expression without GROUP BY clause. If you need this value for each group (if there are duplicates in your output while removing COUNT() expression) you must add GROUP BY for all other fields in output list. Or you can use the window variant of COUNT() function which is scalar. - Akina

Related Question