CODEWITHSUNDEEP
sql-serverms-accessunion
Tristan Forward
Tristan Forward

Reputation: 3514

Joining two queries that have group by's

I'm trying to calculate a rating, a count and an index that is based on dividing the rating by the count.

However I'm stuck at getting the rating and count queries to output to the same result.

I have the following two queries:

SELECT
    DerivedStructuralRatingQuery.Pipe_Segment_Reference,
    SUM(DerivedStructuralRatingQuery.Structural_Rating) AS Structural_Score
FROM
    (
    SELECT Inspections.Pipe_Segment_Reference,
    (COUNT(*) * Conditions.structural_grade) AS Structural_Rating
    FROM (
        SELECT Inspections.Pipe_Segment_Reference, Conditions.structural_grade
        FROM Conditions
        INNER JOIN Inspections
        ON Conditions.InspectionID = Inspections.InspectionID
        WHERE Conditions.structural_grade IS NOT NULL
    )
    GROUP BY Inspections.Pipe_Segment_Reference, Conditions.structural_grade
    ) DerivedStructuralRatingQuery 
GROUP BY
    DerivedStructuralRatingQuery.Pipe_Segment_Reference;

-

SELECT 
    Inspections.Pipe_Segment_Reference,
    COUNT(*) AS Defects
FROM (
    SELECT Inspections.Pipe_Segment_Reference, Conditions.structural_grade
    FROM Conditions
    INNER JOIN Inspections
    ON Conditions.InspectionID = Inspections.InspectionID
    WHERE Conditions.structural_grade IS NOT NULL
    )
GROUP BY Inspections.Pipe_Segment_Reference, Conditions.structural_grade

Desired Output:

ID, Rating, Count
1, 5, 10
2, 3, 4

However I don't know how to combine them into a single output only separate. I tried doing the two queries as one but was getting issues with having two COUNT statements.

I think that I need is a UNION but I can't figure out the syntax if that is the case.

Upvotes: 0

Views: 73

Answers (1)

vmachan
vmachan

Reputation: 1682

I think all you need to do is join results from above 2 queries based on the Pipe_Segment_Reference as shown below

SELECT A.Pipe_Segment_Reference
      ,A.Structural_Score 
      ,B.Defects 
      ,(A.Structural_Score / B.Defects) AS [Index]
  FROM 
       (
         SELECT
               DerivedStructuralRatingQuery.Pipe_Segment_Reference,
               SUM(DerivedStructuralRatingQuery.Structural_Rating) AS Structural_Score
           FROM
                (
                  SELECT Inspections.Pipe_Segment_Reference,
                         (COUNT(*) * Conditions.structural_grade) AS Structural_Rating
                   FROM (
                          SELECT Inspections.Pipe_Segment_Reference, Conditions.structural_grade
                            FROM Conditions
                           INNER JOIN Inspections
                              ON Conditions.InspectionID = Inspections.InspectionID
                           WHERE Conditions.structural_grade IS NOT NULL
                        )
                  GROUP BY Inspections.Pipe_Segment_Reference, Conditions.structural_grade
              ) DerivedStructuralRatingQuery 
         GROUP BY
                  DerivedStructuralRatingQuery.Pipe_Segment_Reference
       ) A
 INNER
  JOIN (
            SELECT 
                   Inspections.Pipe_Segment_Reference,
                   COUNT(*) AS Defects
             FROM (
                      SELECT Inspections.Pipe_Segment_Reference, Conditions.structural_grade
                        FROM Conditions
                       INNER JOIN Inspections
                          ON Conditions.InspectionID = Inspections.InspectionID
                       WHERE Conditions.structural_grade IS NOT NULL
                  )
             GROUP BY Inspections.Pipe_Segment_Reference, Conditions.structural_grade
       ) B
    ON A.Pipe_Segment_Reference = B.Pipe_Segment_Reference

Hope this helps.

NOTE: The above just builds on your queries and does not try to optimize them internally to get the results.

Upvotes: 1

Related Questions