Get the Store count of each state by Join using Oracle

Question

I have a query select * from TBL_RRSOC_STATE_CENTROID; which gives me the data of each and every state.

So by joining with Master table, I want count of the store on the basis of STATE. So I wrote the join query like below

select  cen.X, cen.Y, cen.R4GSTATECODE, cen.R4GSTATENAME, rrs.STATE, COUNT(rrs.STORE_CODE)
from TBL_RRSOC_STATE_CENTROID cen
 INNER JOIN TBL_RRSOC_STORE_INFO RRS ON CEN.R4GSTATENAME = RRS.STATE
order by 1 desc;

But I am getting error as

ORA-00937: not a single-group group function

I want the count of store in each state. Please suggest how to get this

Answer 1

I guess your final query would look like the following

select  cen.X, cen.Y, cen.R4GSTATECODE, cen.R4GSTATENAME, rrs.STATE, 
COUNT(rrs.STORE_CODE)
FROM TBL_RRSOC_STATE_CENTROID cen
LEFT JOIN TBL_RRSOC_STORE_INFO RRS 
ON CEN.R4GSTATENAME = RRS.STATE
GROUP BY cen.X, cen.Y, cen.R4GSTATECODE, cen.R4GSTATENAME, rrs.STATE
ORDER BY 1 desc;