query mysql grupo by and having-CodePudding

I´m traying to create one query for get all dates to commercial.

i need this return:

commercial 1 - 3 commercial 2 - 4 commercial 5 - 2 ...

but i need get thid dates from current date. I´m traying this:

select count(id) 
from cita 
group by id_comercial
having date('created_at') = CURDATE()

this return error if i remove having clausure, return all data from my table how i need, but i need filter by current date.

after i need transform this query in eloquent... But first i have to do in mysql and show result.

My table is:

    CREATE TABLE `cita` (
  `id` int(10) UNSIGNED NOT NULL,
  `nomape` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `direccion` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `provincia` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ciudad` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `cp` char(5) COLLATE utf8mb4_unicode_ci NOT NULL,
  `telefono` varchar(12) COLLATE utf8mb4_unicode_ci NOT NULL,
  `movil` varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `id_comercial` int(10) UNSIGNED DEFAULT NULL,
  `id_comercial2` int(10) UNSIGNED DEFAULT NULL,
  `id_llamada` int(10) UNSIGNED NOT NULL,
  `id_estado` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `cita` (`id`, `nomape`, `direccion`, `provincia`, `ciudad`, `cp`, `telefono`, `movil`, `id_comercial`, `id_comercial2`, `id_llamada`, `id_estado`, `created_at`, `updated_at`) VALUES
(1, 'ASENJO MARTIN JOSE RAFAEL', 'AV DE LA VEGA, 188', 'GRANADA', 'BELICENA', '18101', '958431322', NULL, 13, NULL, 617, 8, '2020-08-11 07:53:30', '2021-08-25 13:01:21'),
(2, 'MARTINEZ JIMENEZ MARIA LUISA', 'AVDA. ANDALUCIA, 3 BAJO F', 'JAÉN', 'TORREPEROGIL', '23320', '953776094', NULL, 44, NULL, 318, 8, '2020-08-11 09:20:55', '2021-08-24 15:41:03'),
(3, 'MARIA VIUDA DE SALVADOR LARA HURTADO', 'CALLE SANTA MARIA, 69', 'JAÉN', 'TORREPEROGIL', '23320', '953776876', NULL, 40, NULL, 578, 8, '2020-08-11 09:25:33', '2021-08-24 15:41:03'),
(4, 'MARTINEZ RUIZ EMILIO', 'CALLE OBISPO BASULTO, 97', 'JAÉN', 'TORREPEROGIL', '23320', '953776072', NULL, 42, NULL, 403, 8, '2020-08-11 09:29:02', '2021-08-24 15:41:03'),
(5, 'PATON REDONDO FERNANDO', 'CALLE DOCTOR FLEMING, 24 SIN LETRA', 'JAÉN', 'TORREPEROGIL', '23320', '953778119', NULL, 43, NULL, 438, 8, '2020-08-11 09:32:16', '2021-08-24 15:41:03'),
(6, 'VILLAR CRESPO JOSE', 'CALLE CERVANTES, 6 CASA', 'JAÉN', 'TORREPEROGIL', '23320', '953776353', NULL, 37, NULL, 769, 8, '2020-08-11 09:37:24', '2021-08-24 15:41:03'),
(7, 'MEDINA GALLEGO CONCEPCION', 'TRVA OBISPO BASULTO, 16', 'JAÉN', 'TORREPEROGIL', '23320', '953776523', NULL, 42, NULL, 404, 8, '2020-08-11 09:46:53', '2021-08-24 15:41:03'),
(8, 'BLANCO ANGUIS MARIA', 'CALLE MURILLO, 18 CASA', 'JAÉN', 'TORREPEROGIL', '23320', '953777328', NULL, 44, NULL, 526, 8, '2020-08-11 09:59:39', '2021-08-24 15:41:03'),
(9, 'MEZGUA MARTINEZ MATILDE', 'B. P. IGLESIAS, 4   2 D', 'JAÉN', 'TORREPEROGIL', '23320', '953778009', NULL, 37, NULL, 852, 8, '2020-08-11 10:00:52', '2021-08-24 15:41:04'),
(10, 'MARQUEZ MUÑOZ ANTONIO BLAS,ROSARIO', 'CALLE REAL DE BELICENA, 9 CASA', 'GRANADA', 'BELICENA', '18101', '958432077', NULL, 35, NULL, 952, 8, '2020-08-11 10:11:16', '2021-08-24 15:41:04'),
(11, 'BLAZQUEZ DELGADO ANTONIO Y EVA', 'CALLE JUAN MANUEL SERRAT, 9 CASA', 'GRANADA', 'BELICENA', '18101', '958431493', NULL, 32, NULL, 692, 2, '2020-08-11 10:13:31', '2020-08-11 13:10:49'),
(12, 'ALANIS GARCIA ANTONIO', 'CALLE RAMÓN Y CAJAL NUMERO 1', 'GRANADA', 'BELICENA', '18101', '958432049', NULL, 36, NULL, 723, 4, '2020-08-11 10:16:47', '2020-08-11 10:33:23'),

thanks for help me and sorry for my english and if my question it´s bad

CodePudding user response：

The having clause is executed after aggregation. And you have only one column -- the count, not the created.

You want a where clause:

select count(id) 
from cita 
where date(created_at) = CURDATE()
group by id_comercial;

Note also that I removed the single quotes. I assume these are a transcription error when you wrote the question.

Also, the where clause would more efficiently be written as:

where created_at >= CURDATE() and
      created_at < CURDATE()   interval 1 day

This helps the optimizer, because there is no function call on the column.