Mysql – SQL – count rows with same value

countgroup byMySQLphpmyadminselect

I would like to group and list all the rows that have the same value (gruppo_muscolare.nome) and count how many values (esercizio.nome) are related to the same value, but if I use GROUP BY then I only get the single results without the full list.

SELECT
gruppo_muscolare.nome,
esercizio.nome,
COUNT(gruppo_muscolare.nome) AS counter
FROM tabella_allenamento, scheda, esercizio_scheda, esercizio, gruppo_muscolare
WHERE tabella_allenamento.id = 29 AND scheda.id_tabella=tabella_allenamento.id
AND esercizio_scheda.id_scheda=scheda.id AND esercizio.id=esercizio_scheda.id_esercizio
AND gruppo_muscolare.id=esercizio.id_gruppo_muscolare
GROUP BY gruppo_muscolare.nome, esercizio.nome, 
gruppo_muscolare.id 
ORDER BY counter DESC

I get:

nome                  nome                 counter 
pettorali          Chest Press               1
pettorali      incline press hammer          1
quadricipiti       Leg Curl                  1

while I would like to get:

nome                  nome                 counter 
pettorali          Chest Press               2
pettorali      incline press hammer          2
quadricipiti       Leg Curl                  1

If I use the GROUP BY statement with only a value:

SELECT gruppo_muscolare.nome,
esercizio.nome, 
COUNT(gruppo_muscolare.nome) AS counter 
FROM tabella_allenamento, scheda, esercizio_scheda, esercizio, gruppo_muscolare
WHERE tabella_allenamento.id = 29 AND scheda.id_tabella=tabella_allenamento.id
AND esercizio_scheda.id_scheda=scheda.id AND esercizio.id=esercizio_scheda.id_esercizio
AND gruppo_muscolare.id=esercizio.id_gruppo_muscolare
GROUP BY gruppo_muscolare.nome
ORDER BY counter DESC

then I get:

nome                  nome                 counter 
pettorali          Chest Press               2
quadricipiti       Leg Curl                  1

that it's what I want but a result is missing.

How can I list ALL the results and at the same time also get the correct counter that counts how many esercizio.nome there are for each gruppo_muscolare.nome?

Thank you!

EDIT: This is the SQL to create and populate all the tables in order to be able to run and test the code.

    -- phpMyAdmin SQL Dump
-- version 5.0.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Creato il: Mar 07, 2021 alle 13:00
-- Versione del server: 10.4.11-MariaDB
-- Versione PHP: 7.4.2

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `pump_db`
--

-- --------------------------------------------------------

--
-- Struttura della tabella `esercizio`
--

CREATE TABLE `esercizio` (
  `id` int(11) NOT NULL,
  `nome` varchar(100) NOT NULL,
  `id_gruppo_muscolare` int(11) NOT NULL,
  `gruppo_muscolare` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dump dei dati per la tabella `esercizio`
--

INSERT INTO `esercizio` (`id`, `nome`, `id_gruppo_muscolare`, `gruppo_muscolare`) VALUES
(1, 'incline press hammer', 1, 'pettorali'),
(2, 'Chest Press ', 1, 'pettorali'),
(3, 'Leg Curl', 2, 'quadricipiti'),
(4, 'spinte con manubri', 3, 'spalle'),
(5, 'Lat machine', 5, 'dorsali'),
(8, 'spalle', 3, 'incline press hammer'),
(10, 'Pulley con triangolo', 5, 'dorsali'),
(11, 'croci su panca piana', 1, 'pettorali');

-- --------------------------------------------------------

--
-- Struttura della tabella `esercizio_scheda`
--

CREATE TABLE `esercizio_scheda` (
  `id` int(11) NOT NULL,
  `id_esercizio` int(11) NOT NULL,
  `id_scheda` int(11) NOT NULL,
  `serie` varchar(50) NOT NULL,
  `ripetizioni` varchar(50) NOT NULL,
  `kg` varchar(50) NOT NULL,
  `note` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dump dei dati per la tabella `esercizio_scheda`
--

INSERT INTO `esercizio_scheda` (`id`, `id_esercizio`, `id_scheda`, `serie`, `ripetizioni`, `kg`, `note`) VALUES
(38, 2, 26, '3', '2', '', ''),
(39, 1, 26, '1', '2', '', ''),
(40, 3, 26, '2', '3', '', ''),
(41, 11, 27, '2', '10-2-1', '', ''),
(42, 2, 27, '3', '2', '', ''),
(43, 1, 27, '1', '2', '', '');

-- --------------------------------------------------------

--
-- Struttura della tabella `gruppo_muscolare`
--

CREATE TABLE `gruppo_muscolare` (
  `id` int(11) NOT NULL,
  `nome` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dump dei dati per la tabella `gruppo_muscolare`
--

INSERT INTO `gruppo_muscolare` (`id`, `nome`) VALUES
(1, 'pettorali'),
(2, 'quadricipiti'),
(3, 'spalle'),
(4, 'glutei'),
(5, 'dorsali'),
(6, 'bicipiti'),
(7, 'tricipiti'),
(8, 'adduttori'),
(9, 'abduttori'),
(10, 'addominali'),
(11, 'femorali');

-- --------------------------------------------------------

--
-- Struttura della tabella `personal_trainer`
--

CREATE TABLE `personal_trainer` (
  `id` int(11) NOT NULL,
  `nome` varchar(50) NOT NULL,
  `cognome` varchar(50) NOT NULL,
  `codice_pt` int(50) NOT NULL,
  `email` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dump dei dati per la tabella `personal_trainer`
--

INSERT INTO `personal_trainer` (`id`, `nome`, `cognome`, `codice_pt`, `email`) VALUES
(1, 'Jacopo', 'Stefano', 1, ''),
(2, 'Federica', 'Metto', 2, ''),
(3, 'Paolo', 'Marsella', 33, '');

-- --------------------------------------------------------

--
-- Struttura della tabella `scheda`
--

CREATE TABLE `scheda` (
  `id` int(11) NOT NULL,
  `id_tabella` int(11) NOT NULL,
  `tipo` varchar(5) NOT NULL,
  `data` date NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dump dei dati per la tabella `scheda`
--

INSERT INTO `scheda` (`id`, `id_tabella`, `tipo`, `data`) VALUES
(26, 29, 'A', '2021-02-28'),
(27, 30, 'A', '2021-02-28');

-- --------------------------------------------------------

--
-- Struttura della tabella `tabella_allenamento`
--

CREATE TABLE `tabella_allenamento` (
  `id` int(11) NOT NULL,
  `id_utente` int(11) NOT NULL,
  `nome` varchar(100) NOT NULL,
  `pt` varchar(100) NOT NULL,
  `data` date NOT NULL DEFAULT current_timestamp(),
  `data_inizio` varchar(100) NOT NULL,
  `data_fine` varchar(100) NOT NULL,
  `recupero_serie` varchar(100) NOT NULL,
  `recupero_esercizio` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dump dei dati per la tabella `tabella_allenamento`
--

INSERT INTO `tabella_allenamento` (`id`, `id_utente`, `nome`, `pt`, `data`, `data_inizio`, `data_fine`, `recupero_serie`, `recupero_esercizio`) VALUES
(29, 1000, 'Rocco Galati #1000-2021-02-28-11-38-07', 'Jacopo Stefano', '2021-02-28', '28-02-2021', '30-03-2021', '1:30\"', '1:30\"'),
(30, 1000, 'Rocco Galati #1000-2021-02-28-11-39-16', 'Jacopo Stefano', '2021-02-28', '28-02-2021', '30-03-2021', '1:30\"', '1:30\"');

-- --------------------------------------------------------

--
-- Struttura della tabella `utente`
--

CREATE TABLE `utente` (
  `id` int(255) NOT NULL,
  `nome` varchar(100) NOT NULL,
  `cognome` varchar(100) NOT NULL,
  `cf` varchar(100) NOT NULL,
  `peso` float NOT NULL,
  `altezza` float NOT NULL,
  `note` varchar(500) NOT NULL,
  `email` varchar(50) NOT NULL,
  `data_di_nascita` varchar(50) NOT NULL,
  `telefono` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dump dei dati per la tabella `utente`
--

INSERT INTO `utente` (`id`, `nome`, `cognome`, `cf`, `peso`, `altezza`, `note`, `email`, `data_di_nascita`, `telefono`) VALUES
(1000, 'Rocco', 'Galati', 'GLTRCC84R04E825L', 80, 178, 'Nessuna nota', 'roccogalati@gmail.com', '04/10/1984', '3280613637'),
(1002, 'Marco', 'Rossi', 'GLTRCC85R04E815L', 90, 180, 'nessuna nota particolare', 'info@robo-dyne.com', '04/10/1985', '3280613637');

--
-- Indici per le tabelle scaricate
--

--
-- Indici per le tabelle `esercizio`
--
ALTER TABLE `esercizio`
  ADD PRIMARY KEY (`id`);

--
-- Indici per le tabelle `esercizio_scheda`
--
ALTER TABLE `esercizio_scheda`
  ADD PRIMARY KEY (`id`);

--
-- Indici per le tabelle `gruppo_muscolare`
--
ALTER TABLE `gruppo_muscolare`
  ADD PRIMARY KEY (`id`);

--
-- Indici per le tabelle `personal_trainer`
--
ALTER TABLE `personal_trainer`
  ADD PRIMARY KEY (`id`);

--
-- Indici per le tabelle `scheda`
--
ALTER TABLE `scheda`
  ADD PRIMARY KEY (`id`);

--
-- Indici per le tabelle `tabella_allenamento`
--
ALTER TABLE `tabella_allenamento`
  ADD PRIMARY KEY (`id`);

--
-- Indici per le tabelle `utente`
--
ALTER TABLE `utente`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT per le tabelle scaricate
--

--
-- AUTO_INCREMENT per la tabella `esercizio`
--
ALTER TABLE `esercizio`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

--
-- AUTO_INCREMENT per la tabella `esercizio_scheda`
--
ALTER TABLE `esercizio_scheda`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=44;

--
-- AUTO_INCREMENT per la tabella `gruppo_muscolare`
--
ALTER TABLE `gruppo_muscolare`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

--
-- AUTO_INCREMENT per la tabella `personal_trainer`
--
ALTER TABLE `personal_trainer`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT per la tabella `scheda`
--
ALTER TABLE `scheda`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;

--
-- AUTO_INCREMENT per la tabella `tabella_allenamento`
--
ALTER TABLE `tabella_allenamento`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

--
-- AUTO_INCREMENT per la tabella `utente`
--
ALTER TABLE `utente`
  MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1007;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Best Answer

Instead of using a GROUP BY clause, use the COUNT() function as a window function so you get all rows back like so:

SELECT
gruppo_muscolare.nome,
esercizio.nome,
COUNT(esercizio.nome) OVER (PARTITION BY 
gruppo_muscolare.nome) AS counter
FROM tabella_allenamento, scheda, esercizio_scheda, esercizio, 
gruppo_muscolare
WHERE tabella_allenamento.id = 29 AND 
scheda.id_tabella=tabella_allenamento.id
AND esercizio_scheda.id_scheda=scheda.id AND 
esercizio.id=esercizio_scheda.id_esercizio
AND gruppo_muscolare.id=esercizio.id_gruppo_muscolare
ORDER BY counter DESC
Related Question