Group By and JOIN within SQL Server (when dealing with multiple columns)

  • I have 2 tables, df1, and df2. I would like to join the two tables and then perform a GROUP BY aggregation.  I am unsure if I am performing the groupby correctly because my values are not adding up correctly as desired.



    host purpose verge name    date
    123 sub hello hello1 1/1/2021
    123 sub peace peace1 1/1/2021
    123 sub love love1 1/1/2021
    123 sub hey hey1 1/1/2021
    455 dem no no1 1/1/2021
    455 dem corp corp1 1/1/2021


    pc  cap free
    123 10 2
    123 20 1
    123 10 2
    123 5 1
    455 5 1
    455 4 3


    host date     pc  cap free purpose 
    123 1/1/2021 123 45 6 sub
    455 1/1/2021 455 9 4 dem



    FROM df1
    JOIN df2 ON = df2.pc
    GROUP BY df1.purpose,,, df2.pc

    I get this error:

    column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

    However, I am not getting the desired output and my values do not add up. Any suggestion is appreciated

    • This topic was modified 1 month ago by  lynhudson1.
  • Maybe as below.  No directly usable data so I can't test it.  This code takes advantage of the fact that is known to be equal to df2.pc.

    sum(df2.cap) AS cap,
    sum( AS free,
    df1.purpose, AS pc
    FROM df1
    JOIN df2 ON = df2.pc
    GROUP BY df1.purpose,,

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

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