How to apply Counts from same table on 3 Coumns?

Question

Hi Bellow is my table and having records. I want to get the counts of Country State and city as per given below result. Country State City India AP Hyderabad India AP Warangal India Gujarat BARODA USA Alaska Phoenix USA Georgia Phoenix India AP Warangal I want to get below results. I want to get County State and City Counts as per given below. How to get this result please give me any idea. Country State City Country Count State Count City Count India AP Hyderabad 3 3 1 India AP Warangal 3 3 2 India Gujarat BARODA 3 1 1 USA Alaska Phoenix 2 1 1 USA Georgia Phoenix 2 1 1 India AP Warangal 3 3 2

sudipta sanyal · Answer

DROP TABLE #temp CREATE TABLE #temp (country NVARCHAR(200)state NVARCHAR(200)city NVARCHAR(200)) GO TRUNCATE TABLE #temp INSERT INTO #temp VALUES ('India''AP''Hyderabad') INSERT INTO #temp VALUES ('India''AP''Warangal1111') INSERT INTO #temp VALUES ('India''Gujarat''BARODA') INSERT INTO #temp VALUES ('India''AP''Hyderabad11') INSERT INTO #temp VALUES ('USA''APAlaska''Phoenix') INSERT INTO #temp VALUES ('USA''Georgia''Phoenix') INSERT INTO #temp VALUES ('India''AP''Warangal') --SELECT * FROM #temp SELECT country state city [country_count] = (SELECT COUNT(*) FROM #temp tt WHERE tt.country = t.country GROUP BY country) [state_count] = (SELECT COUNT(*) FROM #temp tt WHERE tt.state = t.state GROUP BY state) [state_count] = (SELECT COUNT(*) FROM #temp tt WHERE tt.state = t.state GROUP BY state) FROM #temp t GROUP BY countrystatecity

SWAROOP P · Answer

thanks for reply... it is very use full to me.

Country	State	City
India	AP	Hyderabad
India	AP	Warangal
India	Gujarat	BARODA
USA	Alaska	Phoenix
USA	Georgia	Phoenix
India	AP	Warangal