I have a table which consists of multiple columns.

Table consists of data like

I query that table with a condition like

Select Group no from cnt where maxtime<=updatedtime

The output comes A22

Now I want to use this output to query the same table again and get the count of A22 which is 3 with a condition in where clause where I use other columns of the table.

Something like

Select count(group no)
From cnt
Where (effdate<candate)

Effdate and candate are columns of the same table.

CodePudding user response：

Use GROUP BY to get the counts of each group. And combine both conditions with AND.

SELECT group_no, COUNT(*)
FROM cnt
WHERE maxtime <= updatedtime
AND effdate < candate
GROUP BY group_no

CodePudding user response：

I suppose you want this: count the rows with effdate < candate for each group_no for which exists a row with maxtime <= updatedtime.

Select group_no, count(*)
from cnt
where effdate < candate
and group_no in (select group_no from cnt where maxtime <= updatedtime)
group by group_no
order by group_no;