SQL Server how to do aggregate Function with 3 tables [closed]

icomxhvb 于 2023-05-16 发布在其他

关注(0)|答案(1)|浏览(187)

Closed. This question needs details or clarity . It is not currently accepting answers.

Want to improve this question? Add details and clarify the problem by editing this post .

Closed 2 days ago.
Improve this question

i am try to do aggregate Function with 3 tables. and when i am using in sum on on of the table is not working

select 
    t1.EmployeeName,sum(t2.Amount), t2.DateOfTransaction, t3.Department
from 
    
     tblEmployee2 t1
     right join
       tblTransaction t2 on t1.EmployeeNumber = t2.EmployeeNumber
       join
       tblDepartment t3 on t1.EmployeeName = t3.DepartmentHead
group by t2.EmployeeNumber

problem get 
Msg 8120, Level 16, State 1, Line 3
Column 'tblEmployee2.EmployeeName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

sql-server

来源：https://stackoverflow.com/questions/76244248/how-to-do-aggregate-function-with-3-tables

1条答案

按热度按时间

wnrlj8wa1#

When you use the group by, the field in the select must be in the group by or aggregate

you have to use max with group by or write a window function

group by (use max)

select 
   t2.EmployeeNumber,sum(t2.Amount) as sAmount
    , max(t2.DateOfTransaction) as DateOfTransaction
    ,max( t3.Department) Department
from 
    
     tblEmployee2 t1
     right join
       tblTransaction t2 on t1.EmployeeNumber = t2.EmployeeNumber
       join
       tblDepartment t3 on t1.EmployeeName = t3.DepartmentHead
group by t2.EmployeeNumber

Window function

select EmployeeNumber,sAmount,DateOfTransaction,Department from (
        select 
              t2.EmployeeNumber,sum(t2.Amount) over(partition by t2.EmployeeNumber order by t2.EmployeeNumber) as sAmount
            , t2.DateOfTransaction, t3.Department
            ,ROW_NUMBER() over(partition by t2.EmployeeNumber order by t2.EmployeeNumber) rw
        from 
    
             tblEmployee2 t1
             right join
               tblTransaction t2 on t1.EmployeeNumber = t2.EmployeeNumber
               join
               tblDepartment t3 on t1.EmployeeName = t3.DepartmentHead
        --group by t2.EmployeeNumber
)a
where a.rw=1

赞(0）回复(0）举报 2023-05-16

我来回答

SQL Server how to do aggregate Function with 3 tables [closed]

1条答案

相关问题

热门标签

最新问答