CODEWITHSUNDEEP
mysqlsql
arkhamDev
arkhamDev

Reputation: 1048

How to write a sql statement to get a value from all users?

I have an sql statement like so

select users.id, users.username, count(orders.number) as units from users inner join orders on orders.user_id = users.id where users.id = 1;

Now this would the number or units user id one has made.

How do i get all users and the number or units they have purchased. The where keyword expects a value and a specific one, how can i say all users.

Thank you very much for reading this :)

Upvotes: 1

Views: 79

Answers (3)

Uriil
Uriil

Reputation: 12618

You should use GROUP BY:

     SELECT users.id, users.username, count(orders.number) as units 
     FROM users 
     INNER JOIN orders on orders.user_id = users.id 
     GROUP BY users.id, users.username

You can check more here: GROUP BY (Aggregate) Functions

Upvotes: 3

isJustMe
isJustMe

Reputation: 5470

You need to aggregate the data, to do so use GROUP BY

        SELECT
        users.id, 
        users.username,
        count(orders.number) as units 
        FROM users  
        JOIN orders 
        ON orders.user_id = users.id
        GROUP BY users.id, users.username;

Check out the documentation in here

Upvotes: 2

chetan
chetan

Reputation: 2886

if you need all users, remove where clause and group by user id

select users.id, users.username, count(orders.number) as units 
from users inner join orders 
on orders.user_id = users.id 
group by users.id,users.username;

Upvotes: 1

Related Questions