How to write sql to join same tabl

abhilocalcircles

Hi,

 

I am trying to create report using Table transformer using JIRA as source. I would like to display no of tickets those have status='blocked' along with other columns using below query

 

SELECT SUM(T1.'Story Points'::integer) as 'Days Needed'
,count(T1.'Assignee') NoOfTickets
,(select count(T1.'Assignee') from T1 where T1.'Status' LIKE "%BLOCK%") as 'Blocked Tickets'
, T1.'Assignee' as 'Assignee'
,CEIL(DATEDIFF(DAY,NOW(),"17-01-2023")) as 'Calendars Days Left'
FROM T1
group by T1.'Assignee'

 

But, this show blocked tickets for all assignee same if one of them has it. I am not able to corelate assignee to sub query from T1.Assignee table. As only one of the assignee has blocked status tickets but result shows same value for assignees.

 

Please advise.

Katerina Rudkovskaya _Stiltsoft_

Hi @abhilocalcircles,

Seems that the issue comes from the inappropriate usage of the external and internal SELECT statements.

Unfortunately, you didn’t provide us with the screenshot of your original table, but I’ve recreated something similar myself:



Here is my SQL query:
SELECT 'Assignee',
SUM('Story Points'::integer) as 'Days Needed',
COUNT('Assignee') AS 'NoOfTickets',
CEIL(DATEDIFF(DAY,NOW(),"17-01-2023")) as 'Calendars Days Left'
FROM (
SELECT * FROM T*
WHERE T1.'Status' LIKE "%BLOCK%")
GROUP BY 'Assignee'

As you can see, the T1.’Column name’ functions are used only inside the internal SELECT. The external SELECT calls columns by their names only (without the T1. prefix).

In the internal SELECT we filter our table T1 by statuses, but after that we can’t refer to this table as T1 in the external SELECT anymore.


Hope that it helps your case.