Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Get result from a table with a condition and then use the result of it to check another condition in the same table and get the output

I have a table which consists of multiple columns.

Table consists of data like

Group no Maxtime Updatedtime
A22 20221502 20221702
A22 20212502 20221702
A22 20212502 20221702

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.

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search