CODEWITHSUNDEEP
sqlpostgresql
UUser196
UUser196

Reputation: 25

Counting the Number of Rows That Have a Spesific Value in a Column in a Table in SQL

id | street | city | country | postal code |
--------------------------------------------
0  |a street|x city| Turkey  | 12345       | 
1  |b street|y city| Turkey  | 12335       | 
2  |c street|z city| USA     | 12315       | 
3  |d street|j city| Turkey  | 32345       | 
4  |e street|k city| Germany | 12135       | 
5  |f street|l city| France  | 13215       | 
6  |g street|m city| Turkey  | 42135       | 
7  |h street|n city| Italy   | 12135       | 
8  |i street|z city| Spain   | 32115       |

Hello. Let say we have a table like above named 'person_address'. In db there are lots of different tables like this. I want to find the number of rows whose values are "Turkey" in the "country" column in the person_address table which is 4. How can I translate this into postgresql?

SELECT * FROM person_address u WHERE u.country = 'Turkey';

With this code i can list what i want but i need number of that list. And after that i have to use this in Java spring boot project. Should to this with @Query annotation or is there a better way?

Upvotes: 0

Views: 1125

Answers (1)

persian-theme
persian-theme

Reputation: 6638

If you enter the output you want, you will get the answer very quickly. The following query returns the countries that have been listed 4 times.

select *
from
   (select *,
        count(*) over(partition by country order by country) as numberOfCountry
    from person_address) t
where numberOfCountry = 4

Upvotes: 1

Related Questions