troubleshooting Question

Distinct count(*) in oracle query

Avatar of WallaceAdrian
WallaceAdrianFlag for Afghanistan asked on
Oracle Database
3 Comments1 Solution11636 ViewsLast Modified:
My data looks like this

sale_fact table
customer_key   site_key    week_key
1                     100           375
2                     200           376
2                     100           375
3                     100           376

site_dimension table
site_key          site_velocity
100                CAR PARK
200                CAR RENTAL

I want the output as

SITE VELOCITY           TOTAL(distinct count of customer_key for each site_velocity)
CAR PARK                   1
CAR RENTAL               1

I want to count distinct customer keys in a site_velocity and I can sucessfully achieve this with the sql below. Now I want to count distinct customer_key for each site_velocity but avoid the first week_key in the group. If you look at the example above, I have not included count for week 375 as this is the lowest week_key in the group. I do not want to change the where condition as my other columns depend on it.

select site_velocity, sum(totals) from
(
select site_velocity, count(distinct customer_key) over (partition by site_velocity) totals
from sale_fact s, site_dimension site
where s.site_key = site.site_key
  and where week_key between 375 and 427
)
group by site_velocity
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros