JOIN Causing Duplicate Values

  • Dear Group:

    I am missing something and not sure what.

    Below, is the output and query when I do a simple select statement and these numbers are correct.

    SELECT 
    A.ReportDate,
    A.Site,
    FORMAT(A.ReportDate, 'yyy') + '-' + FORMAT(A.ReportDate, 'MM') AS Report_Slicer,
    A.Pfolio, A.C_Type,
    SUM(A.Calls) AS Calls,
    SUM(A.HSec) AS HSec,
    SUM(A.Accept) AS Accept,
    SUM(A.Reject) AS Reject,
    SUM(A.ASec) AS ASec,
    SUM(A.RSec) AS RSec
    FROM
    Output_1 A
    GROUP BY
    A.ReportDate,
    A.Pfolio,
    A.Site,
    A.C_Type
    ReportDateSiteReport_SlicerPfolioC_TypeCallsHSecAcceptRejectASecRSec
    2020-03-01BA2020-03 Brd6032.001803005.00180132939405.8750507.74
    2020-03-01CE2020-03 Brd958.00276525.006338815113.9816216.97

    I am trying to join another table to the above, but when I do, the numbers are all identical and I am not sure what I am missing here.

    SELECT 
    A.ReportDate,
    A.Site,
    FORMAT(A.ReportDate, 'yyy') + '-' + FORMAT(A.ReportDate, 'MM') AS Report_Slicer,
    A.Pfolio, A.C_Type,
    SUM(A.Calls) AS Calls,
    SUM(A.HSec) AS HSec,
    SUM(A.Accept) AS Accept,
    SUM(A.Reject) AS Reject,
    SUM(A.ASec) AS ASec,
    SUM(A.RSec) AS RSec,
    B.LC
    FROM
    Output_1 A
    JOIN LC B ON A.ReportDate = B.Report_Date AND A.Site = B.Site
    GROUP BY
    A.ReportDate,
    A.Pfolio,
    A.Site,
    B.LC,
    A.C_Type
    ReportDateSiteReport_SlicerPfolioC_TypeCallsHSecAcceptRejectASecRSecLC
    2020-03-01BA2020-03Brands6032.001803005.00180132939405.8750507.74LC2
    2020-03-01BA2020-03Brands6032.001803005.00180132939405.8750507.74LC5
    2020-03-01BA2020-03Brands6032.001803005.00180132939405.8750507.74Tenured
    2020-03-01CE2020-03Brands958.00276525.006338815113.9816216.97LC5
    2020-03-01CE2020-03Brands958.00276525.006338815113.9816216.97Tenured

    I am trying to add in the LC, so I get a breakdown of the numbers based on the LC.  As you can see, my data for C_Type, Calls, HSec, Accept are all identical and incorrect.  I am expecting to see a breakdown of all the various data points by LC (the last column) but I am missing something and I am not sure what.

    Was hoping my explanation is information is enough that someone can show me what I am missing here.  For instance, I fully expect 3 records for Site 'BA', as it has three LC values (LC2, LC5, Tenure).

  • Does column LC exist in table A?

    If not, it would seem that this breakdown is not possible.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • deleted answer

  • Unfortunately it does not exist in Table A 🙁

  • AMCiti wrote:

    Unfortunately it does not exist in Table A 🙁

    As the numbers you are summing exist in a form where they are not broken down by LC, you cannot retrospectively add that breakdown, unless there are other columns in Table A from which you can somehow derive LC.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I understand what you are saying.  I appreciate the help and information you gave 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply