Count records in MySQL

Question

Count records in MySQL

Navigation

#1 by (4 votes)

2

I am working with MySQL and I need to make a query about the following table called tbl-reserva that has the following structure:

id
fecha_inicio
fecha_fin
descripcion

The idea is to make a query that counts, how many records are missing 7 days to expire, that is to say reach your fecha_fin , that you have registered, comparing it with the date of the system.

mysql sql

asked by FeRcHo 06.06.2017 в 04:35

source

1 answer

Click on a button in a listview to have an activity open get the objects that are repeated in an array of JSON objects with javascript (JQuery)

score 4 · Answer 1

This could help you:

SELECT count(*)
FROM tbl-reserva 
WHERE fecha_fin >= CURRENT_DATE - INTERVAL 7 DAY

With the function CURRENT_DATE - INTERVAL 7 DAY you are doing a subtraction of the current date against 7 days before, and only the records where the fecha_fin is greater than or equal to the date obtained will be obtained.

Here is an example of how the solution applies:

DROP TABLE IF EXISTS 'tblreserva';

CREATE TABLE tblreserva (
    id int,
    fecha_inicio datetime,
    fecha_fin datetime,
    descripcion varchar(64)
);

INSERT INTO tblreserva (id, fecha_inicio, fecha_fin, descripcion) VALUES (1, '20170101', '20170601', 'Descripción');
INSERT INTO tblreserva (id, fecha_inicio, fecha_fin, descripcion) VALUES (2, '20170101', '20170602', 'Descripción');
INSERT INTO tblreserva (id, fecha_inicio, fecha_fin, descripcion) VALUES (3, '20170101', '20170603', 'Descripción');
INSERT INTO tblreserva (id, fecha_inicio, fecha_fin, descripcion) VALUES (4, '20170101', '20170501', 'Descripción'); #Este registro no cumple con las validaciones

SELECT count(*)
FROM tblreserva
WHERE fecha_fin >= CURRENT_DATE - INTERVAL 7 DAY

The result is:

+---+----------+
|   | count(*) |
+---+----------+
| 1 | 3        |
+---+----------+

Here you can see the demonstration of the solution