code, name and the number of rentals of customers who have made at least ten rentals-CodePudding

Script to install my database:

    -- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Hôte : 127.0.0.1
-- Généré le : lun. 12 sep. 2022 à 15:15
-- Version du serveur : 10.4.24-MariaDB
-- Version de PHP : 7.4.29

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
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 */;

-- Base de données : `edc_dev_2003r`

CREATE TABLE `bareme` (
  `Tranche` int(11) NOT NULL,
  `MontantTranche` int(11) NOT NULL,
  `Points` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `client` (
  `ClientCode` int(11) NOT NULL,
  `ClientNom` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `client` (`ClientCode`, `ClientNom`) VALUES
(123, 'Gaetan'),
(12874, 'Alex'),
(12875, 'Max');

CREATE TABLE `location` (
  `LocationRef` int(11) NOT NULL,
  `Immatriculation` varchar(11) NOT NULL,
  `ClientCode` int(11) NOT NULL,
  `MontantLocation` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `location` (`LocationRef`, `Immatriculation`, `ClientCode`, `MontantLocation`) VALUES
(1, 'AA-229-AA', 12874, 123),
(2, 'AA-229-AB', 12875, 156),
(3, 'BA-229-AA', 12874, 700),
(4, 'AB-229-AA', 12874, 678),
(5, 'AA-229-AB', 12874, 987),
(6, 'AA-229-AB', 12874, 980),
(7, 'AB-229-AA', 12874, 567),
(8, 'AA-229-AA', 12874, 7789),
(9, 'AA-229-AB', 12874, 567),
(10, 'AA-229-AB', 12874, 456),
(11, 'AA-229-AA', 12874, 566),
(12, 'AB-229-AA', 12874, 700),
(13, 'AA-229-AA', 12874, 899),
(14, 'AA-229-AB', 12874, 67);

CREATE TABLE `vehicule` (
  `Immatriculation` varchar(11) NOT NULL,
  `Catégorie` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `vehicule` (`Immatriculation`, `Catégorie`) VALUES
('AA-229-AA', 'A'),
('AA-229-AB', 'B'),
('AB-229-AA', 'C'),
('BA-229-AA', 'D');

ALTER TABLE `bareme`
  ADD PRIMARY KEY (`Tranche`);
ALTER TABLE `client`
  ADD PRIMARY KEY (`ClientCode`);
ALTER TABLE `location`
  ADD PRIMARY KEY (`LocationRef`),
  ADD KEY `ClientCode` (`ClientCode`),
  ADD KEY `Immatriculation` (`Immatriculation`);
ALTER TABLE `vehicule`
  ADD PRIMARY KEY (`Immatriculation`);
ALTER TABLE `location`
  ADD CONSTRAINT `ClientCode` FOREIGN KEY (`ClientCode`) REFERENCES `client` (`ClientCode`),
  ADD CONSTRAINT `Immatriculation` FOREIGN KEY (`Immatriculation`) REFERENCES `vehicule` (`Immatriculation`);
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 */

Query I seek:

The code, name and the number of rentals of customers who have made at least ten rentals.

My query:

Select client.ClientCode, client.ClientNom, (location.LocationRef)
FROM Client
  INNER JOIN location ON client.ClientCode = location.ClientCode
WHERE location.LocationRef > 9
GROUP BY location.ClientCode;

It doesn't work. It shows me the first LocationRef beyond 10 but not the number of rentals made by the client.

CodePudding user response：

Using an ad hoc, temporary table with an INNER JOIN to count how many records over a certain locationRef number: WHERE location.LocationRef > 9, there are for each ClientCode:

SELECT loc.ClientCode, loc.cnt, client.ClientNom 
  FROM client 
    INNER JOIN (
      SELECT ClientCode, COUNT(ClientCode) cnt 
        FROM location 
        WHERE locationRef > 9 
        GROUP BY clientCode
    ) loc ON client.ClientCode = loc.ClientCode
;

Output:

ClientCode | cnt | ClientNom
----------- ----- ----------
12874      | 5   | Alex

The temporary table query:

SELECT ClientCode, COUNT(ClientCode) cnt 
  FROM location 
  WHERE locationRef > 9 
  GROUP BY clientCode

...uses a combination of COUNT AND GROUP BY to determine how many records there are for each ClientCode.

The temporary table must be named, and that name is used to access it's values outside of it:

INNER JOIN ( /* temp query here */ ) loc ON client.ClientCode = loc.ClientCode

In this case using the name loc and referencing fields like this: loc.ClientCode.

Try it here: https://onecompiler.com/mysql/3yftez2tu

To count all records, regardless of locationRef, remove the WHERE locationRef > 9 portion in the temp query:

SELECT loc.ClientCode, loc.cnt, client.ClientNom 
  FROM client 
    INNER JOIN (
      SELECT ClientCode, COUNT(ClientCode) cnt 
        FROM location 
        GROUP BY clientCode
    ) loc ON client.ClientCode = loc.ClientCode
;

Output:

ClientCode | cnt | ClientNom
----------- ----- ----------
12874      | 13  | Alex
12875      | 1   | Max

Try it here: https://onecompiler.com/mysql/3yfte9qpp

CodePudding user response：

Start with a subquery to find all the ClientCode values that have ten or more items in your location table. The aggregating subquery does the counting.

                SELECT ClientCode
                  FROM location
                 GROUP BY ClientCode
                HAVING COUNT(*) >= 10

Then INNER JOIN that subquery in your main query to filter out all but the frequent customers.

SELECT client.ClientCode, client.ClientNom, (location.LocationRef)
  FROM client
 INNER JOIN location ON client.ClientCode = location.ClientCode
 INNER JOIN (
                 SELECT ClientCode
                   FROM location
                  GROUP BY ClientCode
                HAVING COUNT(*) >= 10
) frequent_clients  ON client.ClientCode = frequent_clients.ClientCode;

If you want just one row per client in your result set, try this

SELECT client.ClientCode, client.ClientNom, COUNT(*) number_of_rentals
  FROM client
 INNER JOIN location ON client.ClientCode = location.ClientCode
 INNER JOIN (
                 SELECT ClientCode
                   FROM location
                  GROUP BY ClientCode
                HAVING COUNT(*) >= 10
    ) frequent_clients
          ON client.ClientCode = frequent_clients.ClientCode
GROUP BY client.ClientCode, client.ClientNom

Fiddle here.