how do I return two times using the COUNT(*) function

Asked

Viewed 64 times

6

SELECT COUNT(*) AS contador, hora_efetivada FROM minha_tabela WHERE status=2

I have a counter that returns the total value of status 2 in the database, but in my name column hora_efetivada there are several times. How do I make for my select return the first time and last time?

For example:

|status| hora_efetivada
| 2    | 13:10
| 2    | 13:12
| 2    | 12:01
| 2    | 08:03
| 2    | 18:03
| 2    | 13:03

My SELECT would return COUNT = 6 and the two times (08:03 and 13:13).

I’ll have to implement it another way?

php mysql

  • SELECT COUNT(*) AS contador, MIN(hora_efetivada) as primeiro_horario, MAX(hora_efetivada) as ultimo_horario FROM minha_tabela WHERE status=2 As long as the spine is the type TEAM, otherwise it will be necessary to make a CAST.

  • SELECT 
 COUNT(STATUS) AS TOTAL,
 STATUS,
 SELECT SUBSTRING(HORA_EFETIVADA,-3);
 FROM
 MINHA_TABELA
 GROUP BY
 STATUS

2 answers

7

If the field is of a DATE/TIME type you can use MIN and MAX to return the lowest and highest value of a result group.

SELECT COUNT(*) AS contador, 
  MIN(hora_efetivada) as primeiro_horario,     
  MAX(hora_efetivada) as ultimo_horario 
FROM minha_tabela WHERE status=2

If the column is of type VARCHAR for example, a CAST

SELECT COUNT(*) AS contador, 
  MIN(STR_TO_DATE(hora_efetivada, '%h:%i')) as primeiro_horario,     
  MAX(STR_TO_DATE(hora_efetivada, , '%h:%i')) as ultimo_horario 
FROM minha_tabela WHERE status=2

4

Just give a MIN and a MAX values, and use DATE_FORMAT to format them:

SELECT
    DATE_FORMAT(MIN(hora), "%H:%i") as menor_hora,
    DATE_FORMAT(MAX(hora), "%H:%i") as maior_hora
FROM minha_tabela
WHERE status = 2

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.