Estimate male and female customers by partition

Question

I have five tables that contain artificial data about bank details.

My intention is to prepare query with 2 most often used locations for transactions for the male customers and for the female customers.

In order to do this, I wrote this query:

SELECT c.Gender,
lo.Name, 
COUNT(c.Gender) OVER(PARTITION BY lo.Name) as TotalPerAccount
FROM dbo.Customer AS c
INNER JOIN dbo.Account AS a ON a.CustomerId=c.Id
INNER JOIN dbo.AccountDetails AS acc ON acc.AccountId=a.Id
INNER JOIN dbo.Location as lo ON lo.Id=acc.LocationId
ORDER BY c.Gender

But above code don't give me good results. Actually I get some results from each location. You can see output below:

[![enter image description here][1]][1]

Also here you can see accounts diagram and how is look like relationship between tables.

[![enter image description here][2]][2]

So can anybody help me how to fix this problem ? Final output should look like pic below.

[![enter image description here][3]][3]

Answer 1

I do not have your data set, but I believe the below would provide you with the desired result:

SELECT c.Gender,
lo.Name, 
COUNT(c.Gender) as TotalPerAccount
FROM dbo.Customer AS c
INNER JOIN dbo.Account AS a ON a.CustomerId=c.Id
INNER JOIN dbo.AccountDetails AS acc ON acc.AccountId=a.Id
INNER JOIN dbo.Location as lo ON lo.Id=acc.LocationId
GROUP BY c.Gender, lo.Name
ORDER BY c.Gender

EDIT

If you want to see the most (like top 2 / Top 10) then you can achieve it like this:

SELECT TOP 10
c.Gender,
lo.Name, 
COUNT(c.Gender) as TotalPerAccount
FROM dbo.Customer AS c
INNER JOIN dbo.Account AS a ON a.CustomerId=c.Id
INNER JOIN dbo.AccountDetails AS acc ON acc.AccountId=a.Id
INNER JOIN dbo.Location as lo ON lo.Id=acc.LocationId
GROUP BY c.Gender, lo.Name
ORDER BY TotalPerAccount desc, c.Gender