-1

I am using mysql. I have a database with tables medico, paziente and visita (doctor, patient and visit respectively). I want to get the dates with the maximum number of visits in one day. So I create a subquery that returns the dates with the number of visits of that day, and then filter them out to get only the ones with the max number of visits in one day. This should work, but the subquery after the WHERE tells me it doesn't find the table named R, that is the table result of the subquery before. This is the entire query:

SELECT R.Data, 
       R.Conto 
FROM  ( SELECT Data, 
               COUNT(*) AS Conto 
        FROM visita 
        GROUP BY Data
       ) AS R 
WHERE R.Conto = ( SELECT MAX(R.Conto) FROM R );

Can anyone explain why can't I reference the table from the other subquery and how should I approach this problem the correct way?

Ergest Basha
  • 7,870
  • 4
  • 8
  • 28
Vito Pampinella
  • 11
  • 1
  • A table alias represents a typical row from the table it's defined beside--not the table. So R represents a typical row from the subquery result. R isn't a table, so it certainly isn't the subquery result. – philipxy Sep 15 '22 at 14:04
  • When pinned down & clearly expressed this will be a faq. (Clearly.) Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [research effort](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] – philipxy Sep 15 '22 at 14:06
  • Please ask 1 specific researched non-duplicate question. Please either ask about 1 bad query/function with the obligatory [mre] & why you think it should return something else at the 1st subexpression that it doesn't give what you expect, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do & ideally a [mre]. But please ask about the former 1st because misconceptions in the former will get in the way of understanding the latter. And bad code doesn't tell us what you wish it would do. [ask] [Help] – philipxy Sep 15 '22 at 15:04

1 Answers1

0

A lot easier to grasp if you have version 8 or above using cte

DROP table if exists t;

create table t
(data date);
insert into t values
('2022-09-01'),('2022-09-01'),('2022-09-02'),('2022-09-03'),('2022-09-03');

with cte as
(select data,count(*) cnt from t group by data)
,
cte1 as
(select max(cnt) maxcnt from cte)
select data,cnt 
from cte 
cross join cte1 
where  cnt = maxcnt;

+------------+-----+
| data       | cnt |
+------------+-----+
| 2022-09-01 |   2 |
| 2022-09-03 |   2 |
+------------+-----+
2 rows in set (0.002 sec)
P.Salmon
  • 17,104
  • 2
  • 12
  • 19
  • Thanks, I didn't know about CTEs yet, but they are the thing I needed to use. I simply had to create R as a CTE and then reference it in the subquery. ``` WITH R AS (SELECT Data, COUNT(*) AS Conto FROM visita GROUP BY Data) SELECT R.Data, R.Conto FROM R WHERE R.Conto = (SELECT MAX(R.Conto) FROM R); ``` – Vito Pampinella Sep 15 '22 at 12:34