更好的方法：

SELECT a.agencyid,
       a.name AS Agency,
       a.abbrev,
       aa.no_of_address,
       c.no_of_collaboration
FROM Agency a
LEFT JOIN
  (SELECT agencyid,
          count(agencyid) AS no_of_address
   FROM AgencyAddress
   GROUP BY agencyid) aa ON a.agencyid = aa.agencyid
LEFT JOIN
  (SELECT agencyid,
          count(agencyid) AS no_of_collaboration
   FROM Collaboration
   GROUP BY agencyid) c ON a.agencyid = c.agencyid
ORDER BY a.name;

我认为其他答案更有效。
实现这一点的另一种方法是：

SELECT a.agencyid,
       a.name AS Agency,
       a.abbrev,
  (SELECT count(aa.agencyid)
   FROM AgencyAddress aa
   WHERE aa.agencyid=a.agencyid) AS no_of_address,
  (SELECT count(c.agencyid)
   FROM Collaboration c
   WHERE c.agencyid=a.agencyid) AS no_of_collaboration
FROM Agency a
ORDER BY a.name;

这更容易阅读，但效率不如上面的。

SELECT aa.agencyid, aa.name AS Agency, aa.abbrev, no_of_address, no_of_collaboration 
FROM (
    SELECT a.agencyid, a.name AS Agency, a.abbrev, COUNT(a.agencyid) AS no_of_address
    FROM Agency a
    LEFT JOIN AgencyAddress aa ON aa.agencyid=a.agencyid
    GROUP BY a.name, a.abbrev, a.agencyid
    ) aa
LEFT JOIN (
    SELECT a.agencyid, a.name AS Agency, a.abbrev, COUNT(c.CollaborationID) AS no_of_collaboration
    FROM Agency a
     LEFT JOIN Collaboration c ON c.AgencyID=a.AgencyID
    GROUP BY a.name,a.abbrev,a.agencyid
    ) bb ON aa.agencyid = bb.agencyid AND aa.name = bb.name AND aa.abbrev = bb.abbrev

按aa.name订购
像这样吗？

与此一起工作：

SELECT T1.AgencyID, T1.Agency, T1.abbrev, no_of_address, no_of_collaboration 
FROM (
    SELECT A.AgencyID,A.Name AS Agency,A.abbrev,COUNT(AA.AgencyID) AS no_of_address
    FROM Agency A
    LEFT JOIN AgencyAddress AA ON AA.agencyid = A.agencyid
    GROUP BY A.Name, A.abbrev, A.agencyid
    ) T1
JOIN (
    SELECT A.AgencyID,A.Name AS Agency,A.abbrev,COUNT(C.CollaborationID) AS no_of_collaboration
    FROM Agency A
    LEFT JOIN Collaboration C ON C.AgencyID = A.AgencyID
    GROUP BY A.Name, A.abbrev, A.agencyid
    ) T2 ON T1.AgencyID = T2.AgencyID
ORDER BY T1.Agency