CODEWITHSUNDEEP
sqlcount
Ikram Shabri
Ikram Shabri

Reputation: 567

Count function from 2 tables in database

I have 2 tables like these

rental_tabel

id |    date       |   cust_id               |     driver_number
1     2019-01-02          1                         F 3350 NN
2     2019-04-02          2                         AX 111 Z
3     2019-05-02          3                         S 787  X
4     2019-05-02          4                         T 9090 M
5     2019-06-02          3                         P 8989 L

user_table

cust_id |   name       
1          John          
2          Doe
3          Michael
4          Leonard
5          Steve

How do I count customer name who's shown more than once in the rental table?

I have using count function, and I can count the id shown more than once, but I still can't get the name of customer

Here's my count query

SELECT COUNT(rental_table.cust_id) AS total FROM rental_table WHERE rental_table.cust_id>'1'

and the result is

| total   |
-----------
|   2     |

Where's I can add to get the customer name from that count function?

Upvotes: 1

Views: 42

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

You can use GROUP BY cust_id with the following HAVING clause :

SELECT u.name
  FROM rental_table r
  JOIN user_table u
    ON u.cust_id = r.cust_id
 GROUP BY cust_id
HAVING count(cust_id) > 1

Upvotes: 1

Related Questions