You're on your way to the next level! Join the Kudos program to earn points and save your progress.
Level 1: Seed
25 / 150 points
1 badge earned
Challenges come and go, but your rewards stay with you. Do more to earn more!
What goes around comes around! Share the love by gifting kudos to your peers.
Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!
Join now to unlock these features and more
I have one table (by Excerpt Include macro) inside a Table Transformer macro with following columns
DeptId, ManagerId, EmployeeId, Salary
I want to show total salary by DeptId and ManagerId
I want to order the records in descending order of total salary by department
(Select T1.'DeptId', T1.'ManagerId', SUM(T1.'Salary') 'Dept-Manager Salary'
GROUP BY T1.'DeptId', T1.'ManagerId') AS TA
(Select T1.'DeptId', SUM(T1.'Salary') 'Dept Salary'
GROUP BY T1.'DeptId') AS TB
ON TA.'DeptId' = TB.'DeptId'
TB.'Dept Salary' DESC
Why is this not working as expected?
Hi @Jignesh Patel ,
The JOIN function can be unstable working with several internal SELECTs. We have this improvement on our roadmap, I've linked this question to the ticket to notify you later.
As for now you may use the following structure (don't forget to use the Table Toolbox macro to nest macros if your hosting is Cloud):
We just split your original query into three separate queries.
Select T1.'DeptId', T1.'ManagerId', SUM(T1.'Salary') 'Dept-Manager Salary'
GROUP BY T1.'DeptId', T1.'ManagerId'
Select T1.'DeptId', SUM(T1.'Salary') 'Dept Salary'
GROUP BY T1.'DeptId'
For external TT:
SELECT T1.'DeptId', T2.'Dept Salary', T1.'ManagerId', T1.'Dept-Manager Salary'
FROM T1 JOIN T* ON T1.'DeptId' = T*.'DeptId'
ORDER BY T2.'Dept Salary' DESC
Then the result table will be as following:
Hope this workaround may help.