SQL distinctly arranging column query issue

Viewed 89

I have following table:

dbPratiche :

enter image description here

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

3 Answers
Related