Identifying percentage in Fact Table

Question

I am new in programming and could not find an answer.

I have following dimensions(tables) and fact table:

• Customer: CustomerId, HomeRegion
• Regions: RegionId, RegionName
• MyTime: id, MyHour
• Fact table: CustomerId, RegionId, TimeId, FactId

I must have report as: HomeRegion, Hour, RegionName, UserPercentage.

As shown in the example, only 3.67% people whose home region is A move to B at 9am and so on.

I should create simular one.

The problem is obtainig UserPercentage. Here is the code I did so far.

SELECT c.HomeRegion, mt.myhour as Time, r.RegionName as CurrentRegion,
(SELECT COUNT(*)
        /*number of users who move from their home 
        region to CurrentRegion at specific time*/
)/COUNT(c.CustomerId)*100 as UserPercentage
FROM dbo.FactTable ft 
inner join dbo.Customer c
    ON ft.CustomerId = c.CustomerId
inner join dbo.myTime mt
    ON ft.TimeId = mt.ID
inner join dbo.Regions r
    ON ft.RegionId = r.RegionId
WHERE mt.myhour = '09'
GROUP BY c.HomeRegion, mt.myhour, r.RegionName
ORDER BY c.HomeRegion, r.RegionName

Answer 1

Using the analytical functions
* no need to select or groupby myHour constant
* assuming one Customer should be located in 1 region at once (if not - it would be much harder to select)

select HomeRegion, CurrentRegion, 
count(*) / count(*) over () as overall_share,
count(*) / count(*) over (partition by HomeRegion) as homeregion_share, 
    from
    (SELECT c.HomeRegion, r.RegionName as CurrentRegion, c.CustomerId as CUST
    FROM dbo.FactTable ft 
    inner join dbo.Customer c
        ON ft.CustomerId = c.CustomerId
    inner join dbo.myTime mt
        ON ft.TimeId = mt.ID
    inner join dbo.Regions r
        ON ft.RegionId = r.RegionId
    WHERE mt.myhour = '09'
    GROUP BY c.HomeRegion, r.RegionName, c.CustomerId) uni_users
    GROUP by HomeRegion, CurrentRegion

Answer 2

Try something like this in your comment area.

SELECT (TMP1.Count*100)/COUNT(TMP2.CustomerId) AS 'Percentage' 
FROM 
(
SELECT COUNT(*) AS 'Count' 
FROM dbo.FactTable ft 
inner join dbo.Customer c ON ft.CustomerId = c.CustomerId 
inner join dbo.Regions r ON ft.RegionId = r.RegionId 
WHERE 
r.RegionName IN ('A','B','C','D','E') AND 
c.HomeRegion IN ('A','B','C','D','E') 
) AS 'TMP1', dbo.Customer AS 'TMP2'