CODEWITHSUNDEEP
sqlsql-server
Lynn
Lynn

Reputation: 4408

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 keep getting an error that says the columns are not contained in the aggregate function.

DATA:

df1

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

df2

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

DESIRED

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

DOING

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

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 have aggregated the two columns cap and free. I am still researching this. Any suggestion is appreciated

Upvotes: 1

Views: 52

Answers (1)

Johnny Fitz
Johnny Fitz

Reputation: 542

You could try this instead if you prefer to see all the original data, but you do need to group the non-aggregated columns otherwise:

SELECT df1.host, 
       df1.date, 
       Sum(df2.cap) over (partition by df1.purpose),
       Sum(df2.free) over (partition by df1.purpose),
       df1.purpose,
       df2.pc
FROM   df1
JOIN df2 ON
       df1.host = df2.pc

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15

Upvotes: 2

Related Questions