0

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?

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.