CODEWITHSUNDEEP
phpmysqldatabase
Lukas Jülich
Lukas Jülich

Reputation: 11

SELECT and SELECT COUNT in two tables in mySQL

is it possible to combine SELECT and SELECT COUNT in two tables that are connected with the argument JOIN?

I want to select first and last name of customers and only count the amount of orders for each customer.

The table should display first-, lastname and the amount of orders for each customer, whereas last column should be named 'Anzahl Aufträge'.

enter image description here

enter image description here

I just added two links, so you can see, how the table should look like. :)

Upvotes: 0

Views: 78

Answers (3)

L. Scott Johnson
L. Scott Johnson

Reputation: 4382

You need a group by to collapse the orders to the customer level:

SELECT first_name, last_name, COUNT (*)
FROM customers JOIN orders
ON customers.id = orders.customer_id
GROUP BY customers.id;

Upvotes: 1

Oleg Pnk
Oleg Pnk

Reputation: 332

I think you are mean this:

SELECT firstName, lastName, cnt
FROM (
   SELECT customer_id, count(*) as cnt
   FROM orders
   GRUP BY customer_id
) AS q1
INNER JOIN customers as c on c.customer_id = q1.customer_id

Upvotes: 1

O. Jones
O. Jones

Reputation: 108651

You have not given details so I must guess.

Try this:

       SELECT customer.customer_id, customer.givenname, customer.surname, 
              COUNT(*) number_of_orders
         FROM customer
         JOIN order ON customer.customer_id = order.customer_id
        GROUP BY customer.customer_id, customer.givenname, customer.surname

Upvotes: 2

Related Questions