Joining two subqueries or a query with a subquery SQL Server 2008

Question

monster edit: The query will now run but returns incorrect answers. Added a rough schema. PatientID is not the primary key in the tblPatientVisits table because the same patientID can be present more than once.

Every county name is listed, but each count(s.countyName) and count(t.countyname) as 1

PatientVisits   
PatientID   int
PatientState    varchar(2)
patientCounty   varchar(3)
visitNumber int - PK

tblStateCounties    
CPK stateCode   varchar(2)
CPK countyCode  varchar(3)
countyName  varchar(25)


SELECT t.countyName,
    count(t.countyName) as reAdmits ,count(s.countyName) as totalVisits
FROM (
    SELECT countyName,count(countyName) AS readmitCounts
    FROM (
        SELECT tblPatient.patientID
            ,tblStateCounties.countyName
        FROM tblPatient
        INNER JOIN tblPatientVisits
            ON tblPatient.patientID = tblPatientVisits.patientID
        INNER JOIN tblStateCounties
            ON tblPatientVisits.patientState = tblStateCounties.stateCode
                AND tblPatientVisits.patientCounty = tblStateCounties.countyCode
        GROUP BY tblPatient.patientID
            ,tblStateCounties.stateCode
            ,tblStateCounties.countyName
        HAVING (COUNT(tblPatient.patientID) > 1)
            AND (tblStateCounties.stateCode = '21')
        ) t
    GROUP BY countyname
    ) t
INNER JOIN (
    SELECT countyName
    FROM (
        SELECT tblStateCounties.countyName
            ,COUNT(tblStateCounties.countyName) AS counts
        FROM tblPatient
        INNER JOIN tblPatientVisits
            ON tblPatient.patientID = tblPatientVisits.patientID
        INNER JOIN tblStateCounties
            ON tblPatientVisits.patientState = tblStateCounties.stateCode
                AND tblPatientVisits.patientCounty = tblStateCounties.countyCode
        WHERE (tblStateCounties.stateCode = '21')
        GROUP BY tblStateCounties.countyName
        ) z
    ) s
    ON s.countyName = t.countyName
    group by s.countyname, t.countyname

edit: I have a query that runs now, but it returns

Answer 1

Hard to tell without sample data and desired results, but perhaps this is what you are after?

;WITH x AS 
(
  SELECT c.CountyName, v.patientCounty, v.patientState, p.patientID
    FROM dbo.tblPatient AS p
    INNER JOIN dbo.tblPatientVisits AS v
    ON p.patientID = v.patientID 
    INNER JOIN dbo.tblStateCounties AS c 
    ON v.patientState = c.stateCode 
    AND v.patientCounty = c.countyCode
  WHERE c.stateCode = '21'
),
y AS (SELECT CountyName, c = COUNT(*) FROM x GROUP BY CountyName),
z AS (SELECT CountyName, c = COUNT(PatientID) FROM x 
  GROUP BY CountyName, patientState, PatientID HAVING COUNT(*)>1)
SELECT y.countyName, reAdmits = MAX(COALESCE(z.c, 0)), totalVisits = MAX(y.c)
FROM y LEFT OUTER JOIN z
ON y.CountyName = z.CountyName
GROUP BY y.CountyName;

Answer 2

There appear to be several issues with this query. I assume that the subquery for "s" is intended to be substituted for "s" in the query before. Are you familiar with the "with" syntax? This would be close to how you are expressing it.

In any case, your first subquery is missing a ") " between the HAVING clause and the GROUP BY. In addition, the "s" subquery has a GROUP BY, but "SELECT *".

The following may be what you are trying to express:

select t.countyName, count(t.countyName), s.countyName, count(s.countyName)
from (select countyName, count(countyName) as readmitCounts
      from (SELECT tblPatient.patientID, tblStateCounties.countyName
            FROM tblPatient INNER JOIN
                 tblPatientVisits
                 ON tblPatient.patientID = tblPatientVisits.patientID INNER JOIN
                 tblStateCounties
                 ON tblPatientVisits.patientState = tblStateCounties.stateCode AND
                    tblPatientVisits.patientCounty = tblStateCounties.countyCode
            GROUP BY tblPatient.patientID, tblStateCounties.stateCode, tblStateCounties.countyName
            HAVING (COUNT(tblPatient.patientID) > 1) AND (tblStateCounties.stateCode = '21')
           ) t
      group by countyname
     ) t inner join
     (select tblStateCounties.countyName
      from (SELECT tblStateCounties.countyName, COUNT(tblStateCounties.countyName) AS counts
            FROM tblPatient INNER JOIN
                 tblPatientVisits
                 ON tblPatient.patientID = tblPatientVisits.patientID INNER JOIN
                 tblStateCounties
                 ON tblPatientVisits.patientState = tblStateCounties.stateCode AND 
                    tblPatientVisits.patientCounty = tblStateCounties.countyCode
            WHERE (tblStateCounties.stateCode = '21')
       GROUP BY tblStateCounties.countyName
     )
     on s.countyName = t.countyName