SQL - Doubt in a query

Question

Thanks to the help of Motta I managed to arrive in a satisfactory query but I ran into another question because, I need to list only the smallest record for cod, and count the amount of the column menorregistro by name.

Could someone help?

Follow the return of the query for exemplification:

That is, I need you to list only the lines with the Menorregister column = 558, 896 and 1321. And to count the Menorregistro by name staying that way:

Note: I don’t need the column DT_ATE and even list which was the smallest record I just want to count how many smaller records each name had, I left the columns only to better exemplify the problem.

Follow the query:

select b.pront as cod, 
       d.nome,
       b.dt_ate,
       min(case when b.dt_ate between '19.12.2015' and '08.01.2016' then b.reg else null end) menorRegistro
            from recadate b
            inner join tbcbopro c on b.crm=c.cod
            inner join tbprofis d on c.id_tbprofis=d.id
              where b.conv between :ConvInicial and :ConvFinal
              and b.unidade between :Unidade_inicial and :Unidade_final
              and b.pront in (111811, 210313, 395415) 
group by d.nome, b.pront, b.reg, b.dt_ate 
having min(case when b.dt_ate between '19.12.2015' and '08.01.2016' then b.reg else null end) is not null

Answer 1

EDIT 2:

SELECT cod, nome, MIN(menorregistro) AS menorregistro
FROM (
    SELECT
      b.pront AS cod,
      d.nome,
      b.dt_ate,
      MIN(IIF(b.dt_ate BETWEEN '2015-12-19' AND '2016-01-08', b.reg, NULL)) AS menorregistro
    FROM recadate b
    INNER JOIN tbcbopro c ON b.crm = c.cod
    INNER JOIN tbprofis d ON c.id_tbprofis = d.id
    WHERE 
      AND b.conv    BETWEEN :ConvInicial     AND :ConvFinal
      AND b.unidade BETWEEN :Unidade_inicial AND :Unidade_final
      AND b.pront   IN (111811, 210313, 395415)
    GROUP BY 1, 2, 3
    HAVING MIN(IIF(b.dt_ate BETWEEN '2015-12-19' AND '2016-01-08', b.reg, NULL)) IS NOT NULL
) GROUP BY 1, 2

Answer 2

I used with only to create a table and work with the select you have already defined, after that I only performed a group by Cod and a Count of the smallest records.

with tabela as(
 select b.pront as cod,
        d.nome,
        b.dt_ate,
        min(case when b.dt_ate between '19.12.2015' and '08.01.2016' then b.reg else null end) menorRegistro
             from recadate b
             inner join tbcbopro c on b.crm=c.cod
             inner join tbprofis d on c.id_tbprofis=d.id
               where b.conv between :ConvInicial and :ConvFinal
               and b.unidade between :Unidade_inicial and :Unidade_final
               and b.pront in (111811, 210313, 395415)
 group by d.nome, b.pront, b.reg, b.dt_ate
 having min(case when b.dt_ate between '19.12.2015' and '08.01.2016' then b.reg else null end) is not null
)


 select cod
       ,nome
       ,count(menorRegistro)
   from tabela
 group by cod

Answer 3

You can use a SubSelect to do this, the idea and group your data in the internal select using Min() to take the lowest value and then do the Count() no select from outside.

As follows.

select pront, nome, count(b.reg) as menorRegistro 
from (
    select b.pront, d.nome, min(b.reg) menorRegistro
        from recadate b
        inner join tbcbopro c on b.crm=c.cod
        inner join tbprofis d on c.id_tbprofis=d.id
            where b.conv between :ConvInicial and :ConvFinal
            and b.unidade between :Unidade_inicial and :Unidade_final
            and b.pront in (111811, 210313, 395415) 
    group by d.nome, b.pront, b.reg, b.dt_ate 
)subconsulta
group by nome, pront