I have following table:
dbPratiche :

In this i wanted to arrage distinct [Provincia contraente] (first column) and count of that [Provincia contraente] present in [Provincia Agenzia].
Eg.
If I arrange fist column in distinct with following query:
select distinct([Provincia contraente]) from dbPratiche
Then wanted to give count in next column how many times it has arrived in second column i.e.[Provincia Agenzia]
In simple words, prototypal query:
SELECT DISTINCT( [provincia contraente] ),
Count([provincia agenzia])
FROM dbpratiche
WHERE Distinct([provincia contraente]) = [provincia agenzia]
Ofcourse this query fails, but i made second query with joins as:
SELECT DISTINCT( p1.[provincia contraente] ) AS 'PC',
Count(p2.[provincia agenzia])
FROM dbpratiche p1
INNER JOIN dbpratiche p2
ON p1.[provincia contraente] = p2.[provincia contraente]
AND p2.[provincia agenzia] = p1.[provincia contraente]
GROUP BY p1.[provincia contraente]
But not giving me correct results.
Please help me.
Actual table:
Provincia contraente | Provincia Agenzia
TA TA
CE TA
CE CE
MI FR
MI TA
FR FR
Expected Result:
Provincia contraente | Provincia Agenzia
TA 3
CE 1
MI 0
FR 2
EDIT2:
Also wanted to add count of first column
Provincia contraente | Provincia Agenzia | cnt Provincia contraente
TA 3 1
CE 1 2
MI 0 2
FR 2 1
I tried:
I added COUNT(dbPratiche.[Provincia contraente]) in @verex's query as
SELECT T1.[provincia contraente],
Count(dbpratiche.[provincia agenzia]),
Count(dbpratiche.[provincia contraente])
FROM dbpratiche
RIGHT JOIN (SELECT DISTINCT [provincia contraente]
FROM dbpratiche) AS T1
ON dbpratiche.[provincia agenzia] = T1.[provincia contraente]
GROUP BY T1.[provincia contraente]
But got the count of [Provincia Agenzia]
plz help