pr338
pr338

Reputation: 9120

How to calculate percentile difference in SQL?

Let's say you have 2 tables.

Table 1 - T
Columns
City ID(Foreign keyed to C.cityid)
Completed (yes or no)
NumberOne (integer)
NumberTwo (integer)
Date (timestamp with timezone)

Table 2 - C
City ID (integer)
City Name (string)

For each of the cities 'A' and 'B', how do I calculate 90th percentile difference between NumberOne and NumberTwo for all completed things within the last 30 days.

Here's what I have so far:

SELECT  
    100.0*(T.NumberOne - T.NumberTwo) / T.NumberTwo As PercentDiff
FROM T
JOIN C ON CityID

I'm new to SQL and not sure how to create one query for all of this. Do I need to use subqueries?

Upvotes: 0

Views: 341

Answers (2)

Ihor
Ihor

Reputation: 299

Try something like this:

SELECT 
  100.0*(t.NumberOne - t.NumberTwo) / t.NumberTwo As PercentDiff
from T t
inner join C c
on t.[City ID] = c.[City ID]
where c.Name in ('A','B') 
and t.Completed = 'yes'
and t.Date > DATEADD(day, -30, GETDATE())

Upvotes: 1

user6140865
user6140865

Reputation:

Try ON CITYID = CITYID The in statement expects a Boolean expression.

Upvotes: 0

Related Questions