Search code examples
sqlsql-server

Problem with INNER JOIN with COUNT and GROUP BY

SELECT f.Line,
       q.line,
       COUNT(f.Line) AS FTR_count,
       COUNT(q.line) AS quantity_count
FROM [work_permit].[dbo].[FTR] f
     INNER JOIN [work_permit].[dbo].[quantity] q ON f.Line = q.line
GROUP BY f.Line,
         q.line;

I want FTR_count to return the number of lines present in the FTR table. Similarly I want quantity_count to return the number of lines present in the Quantity table. It is not doing so.

Please help me correct this query.

When I run it, I get wrong values for FTR_count and quantity_count.

I have been asked sample data and desired results. So I'm providing here sample data. Sample data and desired results

Solution

  • You need to pre-aggregate. What you have here is a one to many or, based on that both columns are "wrong", a many to many relationship.

    As such you need to ensure that you pre-aggregate one side so that you are only JOINing to one row, and therefore not "bloating" the number.

    One method to achieve this is a derived table:

    SELECT f.Line,
       --q.line, --Both lines are the same, so why repeat it?
       COUNT(f.Line) AS FTR_count,
       MAX(q.quantity_count) AS quantity_count --using MAX so we don't need to pop it in the GROUP BY
FROM [dbo].[FTR] f
     INNER JOIN(SELECT q.Line,
                       COUNT(q.Line) AS quantity_count
                FROM [dbo].[quantity] q 
                GROUP BY q.Line) q ON f.Line = q.line
GROUP BY f.Line;
         --q.line;