Gather the number of customer by street

I have two tables :

Customer:

id	name	address_id
1	John	4
2	Kate	5
3	Bob	2
4	Michael	2
5	Adriana	3
6	Ann	1

Address:

id	detail_str_name	city	district	street_name
1	France,Paris,str.2,N5	Paris	Paris	str.2
2	France,Parise,str.2 ,N3	Paris	Paris	str.2
3	France, Lille ,str.3,N4	Lille	Lille	str.3
4	France,Paris,str.4,N3	Paris	Paris	str.4
5	France, Paris, Batignolles,N4	Paris	Batignolles	Batignolles

I want table like this:

name	detail_str_name	city	district	street_name	sum(cu.num_cust)
John	France,Paris,str.4,N3	Paris	Paris	str.4	1
Kate	France, Paris, Batignolles,N4	Paris	Batignolles	Batignolles	1
Bob	France,Parise,str.2 ,N3	Paris	Paris	str.2	3
Michael	France,Parise,str.2 ,N3	Paris	Paris	str.2	3
Adriana	France, Lille ,str.3,N4	Lille	Lille	str.3	1
Ann	France,Paris,str.2,N5	Paris	Paris	str.2	3

I want to count customer group by city,district and street_name, not detail_str_name.

I try:

        select cu..name,ad.detail_str_name, ad.city,ad.district, ad.street_name,sum(cu.num_cust)
        from
           (select address_id, name,count (id) as num_cust
           from customer
           group by address_id,name) cu
        left join address ad on cu.address_id = ad.id
        group by cu..name,ad.detail_str_name, ad.city,ad.district, ad.street_name

But,this code groups by detail_str_name, Which does not suit me.

What can I change?

Answer

I haven’t been able to check this so it might not be totally correct but I think the query below should get the data you require.

This SQLTutorial article on the partition by clause might be useful.

SELECT cu.name,
    ad.detail_str_name,
    ad.city,
    ad.district,
    ad.street_name,
    COUNT(cu.name) OVER(PARTITION BY ad.city, ad.district, ad.street_name) AS 'num_cust'
FROM customer cu
JOIN address ad ON ad.id = cu.address_id