CODEWITHSUNDEEP
sql
teheheugh
teheheugh

Reputation: 17

SQL Maximum number of orders

I have this task:

Write a query to find out in which year the maximum number of orders was made by the company. How do i write it?

This is everything I could do, but it is not what I need at all...

SELECT company_name, order_date
 FROM customers
 INNER JOIN orders
 ON customers.order_id = orders.order_id
 WHERE order_date = (SELECT MAX(order_date) FROM orders)
 GROUP BY customer_id, order_date;

Upvotes: 0

Views: 692

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I think you want to aggregate by year and pull the top row. In standard SQL, that would be:

SELECT EXTRACT(year FROM o.order_date) as yyyy, COUNT(*) as num_orders
FROM orders o
GROUP BY EXTRACT(year FROM o.order_date)
ORDER BY COUNT(*) DESC
FETCH FIRST 1 ROW ONLY;

Note that the exact syntax might vary by database. For instance, many databases support YEAR() in addition to or instead of EXTRACT(). Not all databases support FETCH FIRST. Some use TOP or LIMIT. But the above is standard SQL which is the tag on this question.

Also, the customers table is not needed for the query. No JOIN is needed.

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try the below options - choose top/limit based on your DBMS

If SQL Server

SELECT top 1 company_name, year(order_date),count(orders.order_id) as total_order
FROM customers
INNER JOIN orders
ON customers.order_id = orders.order_id
GROUP BY company_name, year(order_date)
order by total_order desc

OR

If MySQL:

SELECT company_name, year(order_date),count(orders.order_id) as total_order
FROM customers
INNER JOIN orders
ON customers.order_id = orders.order_id
GROUP BY company_name, year(order_date)
order by total_order desc limit 1

Upvotes: 1

Related Questions