Question: I have 2 tables in sql server:
Employee (Job Title, Hire Date)

* Person (FirstName, LastName)
And I need to return the E.JobTitle, E.HireDate,P.FirstName ,P.LastName along with the how many employees have the same job title.

so I used this query:

```sql
SELECT E.JobTitle,
       E.HireDate,
       P.FirstName,
       P.LastName,
       COUNT(E.JobTitle)
FROM AdventureWorks2019.HumanResources.Employee E
     JOIN AdventureWorks2019.Person.Person P ON E.BusinessEntityID = P.BusinessEntityID
GROUP BY E.JobTitle,
         E.HireDate,
         P.FirstName,
         P.LastName;

```

the problem is that the query returns 1 per the count, while I'd expect to get per each row the count of the num employees with that job title.
my question is how can I get the correct count?

Answer: 
If the entries in `A` are a non-string iterable (like a `list` or `tuple`, e.g.), you can use pandas string indexing:

```
df['A'].str[1]

```

Full example:

```
>>> import pandas as pd

>>> a = (3, 2, 1)
>>> df = pd.DataFrame([[a], [a], [a]], columns=['A'])
>>> df
           A
0  (3, 2, 1)
1  (3, 2, 1)
2  (3, 2, 1)

>>> df['A'].str[1]
0    2
1    2
2    2
Name: A, dtype: int64

```

If the entries are strings, you can use pandas string methods to split them into a list and apply the same approach above:

```
>>> import pandas as pd

>>> a = '3,2,1'
>>> df = pd.DataFrame([[a], [a], [a]], columns=['A'])
>>> df
       A
0  3,2,1
1  3,2,1
2  3,2,1

>>> df['A'].str.split(',').str[1]
0    2
1    2
2    2
Name: A, dtype: object

```
**If column A contain string values:**

```
import pandas as pd

data = {
    "A" :["3, 2, 1","3, 2, 1", "3, 2, 1"],
    "B" : ["5, 6, 7", "5, 6, 7", "5, 6, 7"]
}
df = pd.DataFrame(data)
output = df["A"].apply(lambda x:  (x.split(",")[1]).strip()).to_list()

print(output)

```

**Result:**

```
['2', '2', '2']

```