Loading

How to apply Counts from same table on 3 Coumns?

How to apply Counts from same table on 3 Coumns?

SWAROOP P

12y1.3k

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

sudipta sanyal12y ago
Accepted 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
0
SWAROOP P12y ago
thanks for reply... it is very use full to me.
0