JOIN Causing Duplicate Values

  • AMCiti

    Old Hand

    Points: 344

    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
    ReportDate	Site	Report_Slicer	Pfolio	C_Type	Calls	HSec	Accept	Reject	ASec	RSec
    2020-03-01 BA 2020-03 Brd 6032.00 1803005.00 180 1329 39405.87 50507.74
    2020-03-01 CE 2020-03 Brd 958.00 276525.00 63 388 15113.98 16216.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
    ReportDate	Site	Report_Slicer	Pfolio	C_Type	Calls	HSec	Accept	Reject	ASec	RSec	LC
    2020-03-01 BA 2020-03 Brands 6032.00 1803005.00 180 1329 39405.87 50507.74 LC2
    2020-03-01 BA 2020-03 Brands 6032.00 1803005.00 180 1329 39405.87 50507.74 LC5
    2020-03-01 BA 2020-03 Brands 6032.00 1803005.00 180 1329 39405.87 50507.74 Tenured
    2020-03-01 CE 2020-03 Brands 958.00 276525.00 63 388 15113.98 16216.97 LC5
    2020-03-01 CE 2020-03 Brands 958.00 276525.00 63 388 15113.98 16216.97 Tenured

    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).

  • Phil Parkin

    SSC Guru

    Points: 244589

    Does column LC exist in table A?

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Jonathan AC Roberts

    SSCoach

    Points: 17298

    deleted answer

  • AMCiti

    Old Hand

    Points: 344

    Unfortunately it does not exist in Table A 🙁

  • Phil Parkin

    SSC Guru

    Points: 244589

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • AMCiti

    Old Hand

    Points: 344

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

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

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