Need help with counts in straight table

[rvijayanth]

Sample data:

Patient ID	City	State
101	ABC	State 1
101	XYZ	State 2
103	XYZ	State 2
103	BCD	State 1
104	BCD	State 1
105	BCD	State 1
106	ABC	State 1
107	XYZ	State 2
108	ABC	State 1
109	ABC	State 1
110	ABC	State 1
108	ABC	State 1
106	ABC	State 1
105	XYZ	State 2
103	XYZ	State 2
101	ABC	State 1
109	ABC	State 1
106	XYZ	State 2
102	ABC	State 1
105	BCD	State 1

Above shown data is patient ID for three cities:

Cities ABC and BCD belonging to State 1 and city XYZ belonging to State 2. 

ABC city has 10, BCD has 4 and XYZ has 6 patients.

So State 1 has a total of 14 patients and State 2 has total of 6 patients. 

I want ONE straight table like shown below for comparison:

City	Total Patients in City	State	Total patients in State corresponding to city	Total patients in other cities of State
ABC	10	State 1	14	4
BCD	4	State 1	14	10
XYZ	6	State 2	6	0

I need help with "Total patients in State corresponding to each city" and "Total patients in other cities of State"

[Anil_Babu_Samineni]

I agree with you !! 

But, Requester must like the option which we gave and That's where community got updated using that facility to put many answers together from Lithium portal 🙂 Happy now ..!

[OmarBenSalem]

I always give right answers but requesters rarely like anything. Not everyone is aware of such an option maybe?
Anyway, thank u for ur contribution 🙂

[rvijayanth]

Thanks for your reply. It helped me  a lot.