Stan Stan - 1 month ago 8
MySQL Question

I can't get max from counted value in sql

I have two tables.Table t1 has columns(ID,Name) and t2 has columns(ID,Date,Time)...I want to run a query like

select t1.Name,t2.ID,count(t2.Date)as Present_Days,MAX(x) as total
FROM (SELECT COUNT(t2.ID) AS x FROM t2 GROUP BY ID) as y
from t1
INNER JOIN t2 on t1.ID=t2.ID
group by Name


When I remove this line "MAX(x) as total FROM (SELECT COUNT(t2.ID) AS x FROM t2 GROUP BY ID) as y" from query, it gives the result..but the original query doesnot work...What can be done to run this query.

Answer

Wrong FROM ( ) dinamic table and two FROM instead of a proper inner join could be you are looking for something like this

  select 
       t1.Name
      ,t2.ID
      ,count(t2.Date) as Present_Days
      ,MAX(y.x) as total 
  FROM ( 
          SELECT t2.ID, COUNT(t2.ID) AS x 
          FROM t2 
          GROUP BY ID
        )  y 
  INNER  JOIN  t1 on y.ID = t1.ID
  INNER JOIN t2 on t1.ID=t2.ID 
  group by t1.Name, t2.ID

and if you need another column named time from t2 you could use

  select 
       t1.Name
      ,t2.ID
      ,count(t2.Date) as Present_Days
      ,MAX(y.x) as total 
      , t2.`time`
  FROM ( 
          SELECT t2.ID, COUNT(t2.ID) AS x 
          FROM t2 
          GROUP BY ID
        )  y 
  INNER  JOIN  t1 on y.ID = t1.ID
  INNER JOIN t2 on t1.ID=t2.ID 
  group by t1.Name, t2.ID, t2.`time`

do the fact that you are join the table t2 you can directly select t2.time in the main select ..

Anyway you could select the t2.time also from the subselect adding this column properly in subquery and using a proper alias like column my_time in the sample below

  select 
       t1.Name
      ,t2.ID
      ,count(t2.Date) as Present_Days
      ,MAX(y.x) as total 
      , y.my_time
  FROM ( 
          SELECT t2.ID, t2.`time` as my_time, COUNT(t2.ID) AS x 
          FROM t2 
          GROUP BY ID
        )  y 
  INNER  JOIN  t1 on y.ID = t1.ID
  INNER JOIN t2 on t1.ID=t2.ID 
  group by t1.Name, t2.ID, t2.`time`
Comments