RDBMS enter image description here
I'm trying to gather the information that is required in this question, I have calculated clients age, here are the data for this table
CREATE TABLE `clientes` (
`id_cliente` int(11) NOT NULL,
`nombre` varchar(45) COLLATE utf8_bin NOT NULL,
`apellidos` varchar(45) COLLATE utf8_bin NOT NULL,
`dni_pasaporte` text COLLATE utf8_bin NOT NULL,
`telefono` mediumtext COLLATE utf8_bin DEFAULT NULL,
`sexo` mediumtext COLLATE utf8_bin NOT NULL,
`fecha_nacimiento` date NOT NULL,
`pais_nacimiento` varchar(45) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--
-- Dumping data for table `clientes`
--
INSERT INTO `clientes` (`id_cliente`, `nombre`, `apellidos`, `dni_pasaporte`, `telefono`, `sexo`, `fecha_nacimiento`, `pais_nacimiento`) VALUES
(1, 'Carlos', 'Montero Suarez', '16066793V', '723510602', 'H', '1980-02-14', 'Spain'),
(2, 'Allison', 'Lukianov', '3258879821', '395404877', 'femenino', '1970-12-06', 'China'),
(3, 'Rey', 'Roja', '8298779244', '352682209', 'H', '1965-05-19', 'Spain'),
(4, 'Elton', 'Saintsbury', '446046705', '969547852', 'H', '1987-08-23', 'Tunisia'),
(5, 'Bert', 'Leckey', '732411565', '625573861', 'H', '1990-09-27', 'China'),
(6, 'Carmine', 'Eicke', '300115949', '261559877', 'femenino', '1985-08-03', 'China'),
(7, 'Maria ', 'Sanchez Cardoso', '14180343D', '441952642', 'femenino', '1979-05-21', 'Spain'),
(9, 'John ', 'Lennon', '236147952', '630745219', 'H', '1970-11-14', 'Tunisia'),
(10, 'Karla', 'Watson', '523617420', '923541078', 'femenino', '1980-05-12', 'Tunisia');
ALTER TABLE `clientes`
ADD PRIMARY KEY (`id_cliente`),
ADD UNIQUE KEY `Id_cliente_unique` (`id_cliente`) USING BTREE;
ALTER TABLE `clientes`
MODIFY `id_cliente` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
What I need to calculate the average age between all 10 users on this table which is 38.2. And I need to have all spanish users that are above this age, can you let me know how to get this information?
I have tried the following entry and I'm just getting the age of all clients
SELECT *,
YEAR(CURDATE()) - YEAR(fecha_nacimiento) - (RIGHT(CURDATE(), 5) < RIGHT(fecha_nacimiento, 5)) Age
from clientes
I want to know is how to calculate the average of all 10 clients based on current day and date of birth.
SELECT *,
YEAR(CURDATE()) - YEAR(fecha_nacimiento) - (RIGHT(CURDATE(), 5) < RIGHT(fecha_nacimiento, 5)) Age
(select avg(all(year(now()) - year(fecha_nacimiento)))) as Average
from clientes
but this only shows one client, how can I get the other two?
