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 country,state,city

SWAROOP P · Answer

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

How to apply Counts from same table on 3 Coumns?

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (2)