Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,361,794
Community Members
 
Community Events
168
Community Groups

Table Transformer Macro - JOIN query

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
But
I want to order the records in descending order of total salary by department


Select TA.*
FROM
           (Select T1.'DeptId', T1.'ManagerId', SUM(T1.'Salary') 'Dept-Manager Salary'
            FROM T1
            GROUP BY T1.'DeptId', T1.'ManagerId') AS TA
     JOIN
           (Select T1.'DeptId', SUM(T1.'Salary') 'Dept Salary'
            FROM T1
            GROUP BY T1.'DeptId') AS TB
       ON TA.'DeptId' = TB.'DeptId'
ORDER BY
        TB.'Dept Salary' DESC

Why is this not working as expected?

1 answer

1 accepted

4 votes
Answer accepted

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

Fri 8-1.png

We just split your original query into three separate queries.

For TT1:

Select T1.'DeptId', T1.'ManagerId', SUM(T1.'Salary') 'Dept-Manager Salary'
FROM T1
GROUP BY T1.'DeptId', T1.'ManagerId'

For TT2:

Select T1.'DeptId', SUM(T1.'Salary') 'Dept Salary'
FROM T1
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:

Fri 8-2.png

Hope this workaround may help.

Thanks for your quick response, @Katerina Kovriga _Stiltsoft_ 
This workaround works for us for now.

Would like to hear when JOIN function is updated as you mentioned.

Thanks a lot, again!

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

Watch 4 Confluence apps compete for Best App Demo in September's Appy Hours

Calling all collaborators! Appy Hours on the Atlassian Community is a monthly event where 4 Partner and app vendor presenters go head-to-head with 5-minute demos for the title of Best App Demo. I...

598 views 0 13
Read article

Atlassian Community Events