CODEWITHSUNDEEP
mysqlsqlcount
smith
smith

Reputation: 379

how to count the number of items in sql

i have such tables:

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) );

and 

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    amount DOUBLE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

I need to select number of orders for every customer

i have tried:

SELECT orders, COUNT(*)
FROM orders
ORDER BY COUNT(*) DESC

but it dosnt work

Upvotes: 0

Views: 72

Answers (2)

Sush
Sush

Reputation: 317

You can write following query also

`SELECT 
    c.customer_id, COUNT(o.order_id)
FROM
    customers c,
    orders o
WHERE
    c.customer_id = o.customer_id
GROUP BY c.customer_id;`

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You can do as

select
c.customer_id,
count(o.customer_id) as total
from customers c
join orders o on o.customer_id = c.customer_id
group by c.customer_id
order by total desc

Above query will return count only for the customers which has data in the orders table and if you need to count for all the customer irrespective of there is data in the orders table or not you need left join

select
c.customer_id,
coalesce(count(o.customer_id),0) as total
from customers c
left join orders o on o.customer_id = c.customer_id
group by c.customer_id
order by total desc

Upvotes: 1

Related Questions