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

Question

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

Navigation

#1 by (6 votes)
#2 by (4 votes)

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 column named hora_efetivada there are several times. How do I make my select return the first time and the 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 both times ( 08:03 and 13:13 ).

Will I have to implement it in a different way?

php mysql

asked by anonymous 26.11.2015 / 19:43

2 answers

What is and what is php.ini for? Application of more than one design pattern

score 6 · Answer 1

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 set.

>

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, you need to do 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

score 4 · Answer 2

Just give% and MIN in values, and use MAX 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