Nested Aggregate function in having clause MySql

user_11468407 0
Viewed 118

This is my schema and sample data:

-- -----------------------------------------------------
-- Table `Music_store`.`customers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Music_store`.`customers` (
  `customer_id` INT NOT NULL AUTO_INCREMENT,
  `customer_firstname` VARCHAR(45) NOT NULL,
  `customer_lastname` VARCHAR(45) NOT NULL,
  `customer_mobno` VARCHAR(20) NOT NULL,
  `music_store_store_id` INT NOT NULL,
  PRIMARY KEY (`customer_id`),
  INDEX `fk_customers_music_store1_idx` (`music_store_store_id` ASC) VISIBLE,
  UNIQUE INDEX `customer_mobno_UNIQUE` (`customer_mobno` ASC) VISIBLE,
  CONSTRAINT `fk_customers_music_store1`
    FOREIGN KEY (`music_store_store_id`)
    REFERENCES `Music_store`.`music_store` (`store_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Music_store`.`orders`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Music_store`.`orders` (
  `order_id` INT NOT NULL AUTO_INCREMENT,
  `order_date` DATETIME NOT NULL,
  `ship_amount` INT NOT NULL,
  `tax_amount` INT NOT NULL,
  `ship_date` DATETIME NOT NULL,
  `customers_customer_id` INT NOT NULL,
  PRIMARY KEY (`order_id`),
  INDEX `fk_orders_customers1_idx` (`customers_customer_id` ASC) VISIBLE,
  CONSTRAINT `fk_orders_customers1`
    FOREIGN KEY (`customers_customer_id`)
    REFERENCES `Music_store`.`customers` (`customer_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

INSERT INTO customers (customer_firstname, customer_lastname, customer_mobno, music_store_store_id)
        VALUES 
("Prasanth", "Pandiselvan", "456-986-1234", 1),
("Dan", "Druar", "347-234-9876", 1),
("Lukeman", "Hakkim", "488-234-0987", 2),
("Paul", "Antony", "356-257-1233", 2),
("Nikhil", "Reddy", "678-234-1345", 3),
 ("Saravana", "Kumar", "456-986-2671", 3),
("Mugil", "Pandian", "877-285-3322", 4);

INSERT INTO orders (order_date, ship_amount, tax_amount, ship_date, customers_customer_id)
        Values
("2020-03-03 22:59:52", "127", 4,  "2020-03-04 22:59:52", 1),
("2020-03-13 22:59:52", "158", 5,  "2020-03-15 22:59:52", 1),
("2020-02-08 22:59:52", "201", 5,  "2020-02-10 22:59:52", 2),
("2020-02-25 22:59:52", "300", 6,  "2020-02-27 22:59:52", 2),
("2020-05-14 22:59:52", "500", 7,  "2020-05-16 22:59:52", 3),
("2020-02-08 22:59:52", "557", 7,  "2020-02-12 22:59:52", 3),
("2020-06-19 22:59:52", "658", 7,  "2020-06-21 22:59:52", 4);

For example this would be the query result for the query

select customers.customer_id, count((orders.customers_customer_id)) as mac
from customers inner join orders on customers.customer_id = orders.customers_customer_id
group by orders.customers_customer_id;

result:

# customer_id, count_of_orders
     '1',         '2'
     '2',         '2'
     '3',         '2'
     '4',         '1'

I want to get the the customer / customers who has maximum orders. I am unable to use MAX(count()). But I want to return one or many rows which has maximum orders.

mysql
2 Answers

As you onl y wanted the ids, you don't need to join the customers table

SELECT customers_customer_id, COUNT(*) count_r 
FROM orders
GROUP BY customers_customer_id
HAVING count_r = ( 
SELECT MAX(count_r) FROM (SELECT COUNT(*) count_r FROM `orders` GROUP BY customers_customer_id) t1)

customers_customer_id | count_r
--------------------: | ------:
                    1 |       2
                    2 |       2
                    3 |       2

db<>fiddle here

to select all wanted columns from Orders and to only include only ids , what have the highest number of orders, I have to do two nested Queries to get the highest numerb of orders .

  1. Select the numbers of orders for ever customers_customer_id
  2. As i need only the highest count, i select the MAX from all count i gathered in the first Select
  3. i use this to filter out all customers_customer_ids which has the highest nu7mer of orders.

The HAVING is needed, because i have to run the Max numbers against the columns count_r, which i can not do on a WHERE clause.

user_51935360

If you expect only one customer with the maximum number of orders, then a LIMIT query is probably the easiest way to do this:

SELECT c.customer_id, COUNT(o.customers_customer_id) AS mac
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customers_customer_id
GROUP BY o.customers_customer_id
ORDER BY mac DESC
LIMIT 1;

If you need to cater to there being possibly 2 or more customers tied for the highest order count, and you are using MySQL 8+, then I suggest using the RANK analytic function here:

WITH cte AS (
    SELECT c.customer_id, COUNT(o.customers_customer_id) AS mac,
           RANK() OVER (ORDER BY COUNT(o.customers_customer_id) DESC) rnk
    FROM customers c
    INNER JOIN orders o ON c.customer_id = o.customers_customer_id
    GROUP BY o.customers_customer_id
)

SELECT customer_id, mac
FROM cte
WHERE rnk = 1;
user_18632290
Related