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.

EXAMPLE

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

What is the problem? Are you getting an error, incorrect results, etc? — Andrew, Dec 09 '13 at 18:14
Well, not a whole lot we can tell you without tons more information. Basically, you're going to need to work out who to identify someone who has switched regions, and incorporate that into a case statement for your count. — Andrew, Dec 09 '13 at 18:23

Aditya · Answer 1 · 2013-12-09T19:12:12.930

1

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'

edited Dec 09 '13 at 19:12

answered Dec 09 '13 at 18:22

Aditya

2,299
5
32
54

which table should I use in stead of TABLE_NAME – Ulugbek Dec 09 '13 at 18:36
Use the table which has the CURRENT REGION , HOME REGION. If these fields are from different tables, you can also use join here. – Aditya Dec 09 '13 at 18:40
Yes they are in different tables as shown at the beginning, but it is not possible to connect only these tables, we need to connect them to the fact table first. However the result is zeros (0). – Ulugbek Dec 09 '13 at 18:45
(SELECT 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') )/COUNT(c.CustomerId)*100 as UserPercentage – Ulugbek Dec 09 '13 at 18:46
Yes. execute this query & see if it gives proper results or not. – Aditya Dec 09 '13 at 18:47
Execute the updated query, replace column name or table name if any. This should definitely produce results otherwise you are making a mistake in your select logic. – Aditya Dec 09 '13 at 19:13

Shooorf · Accepted Answer · 2013-12-09T19:05:28.010

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

edited Dec 09 '13 at 19:05

answered Dec 09 '13 at 18:49

Shooorf

146
3

I had this error: Column 'uni_users.CustomerId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – Ulugbek Dec 09 '13 at 18:58
Column 'uni_users.CUST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – Ulugbek Dec 09 '13 at 19:10
Try last version. U dont need to count customers at all, count(*) is equal in this case – Shooorf Dec 09 '13 at 19:17

Identifying percentage in Fact Table

2 Answers2