CTE or Partition on group by ?

websss
websss used Ask the Experts™
on
Hi

I have the following query

SELECT
      Count(DeviceId) as DeviceCount
      ,b.RootResellerId
      
  FROM Anayltics_Billing_DevicesReporting  b
  where DeviceId not in (863286020087590,869606020199992,869606020199993)
	and ReadingDate between '2020-01-10'  and '2020-01-31'
	Group by 	b.RootResellerId 

	order by DeviceCount desc

Open in new window



this outputs results like this:
DeviceCount	RootResellerId
3673	3071
2303	3449
1533	2990
1153	2975
1068	2299

Open in new window



I know need to do a join on
tblReseller.ResellerId = b.RootResellerId
to display tblReseller.ResellerName
This will enable me to see the reseller name on each row

However the inner join fails, i've read i may need to use CTE or partition but not sure how you add this to the end of the query.

sql server 2017
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
arnold
Distinguished Expert 2017

Commented:
Cute common table expressions sets up a table in memory that can be used to join other data.

What are the data sets you need to combine

;with tablename  as (select)

Select * from sometable join tablename on ...


It is difficult for me to interpret your query to know the underlying data/relationship.
OMC2000

Commented:
try left outer join for or another side:
select tblReseller.ResellerName, DeviceCount
from
(SELECT
      Count(DeviceId) as DeviceCount
      ,b.RootResellerId
      
  FROM Anayltics_Billing_DevicesReporting  b
  where DeviceId not in (863286020087590,869606020199992,869606020199993)
	and ReadingDate between '2020-01-10'  and '2020-01-31'
	Group by 	b.RootResellerId 
) x left outer join tblReseller 
on tblReseller.ResellerId = x.RootResellerId
order by DeviceCount desc

Open in new window


select tblReseller.ResellerName, x.DeviceCount
from
tblReseller left outer join
(SELECT
      Count(DeviceId) as DeviceCount
      ,b.RootResellerId
      
  FROM Anayltics_Billing_DevicesReporting  b
  where DeviceId not in (863286020087590,869606020199992,869606020199993)
	and ReadingDate between '2020-01-10'  and '2020-01-31'
	Group by 	b.RootResellerId 
) x 
on tblReseller.ResellerId = x.RootResellerId
order by DeviceCount desc

Open in new window

Partha MandayamTechnical Director

Commented:
Please post the full query you have written which is failing
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Not sure you need a CTE of window clause (partition by).

If you just want to add the name, simple join should work:
SELECT
      Count(DeviceId) as DeviceCount
      ,b.RootResellerId
		,r.ResellerName
  FROM Anayltics_Billing_DevicesReporting  b
		joon tblReseller r on r.ResellerId = b.RootResellerId
  where DeviceId not in (863286020087590,869606020199992,869606020199993)
	and ReadingDate between '2020-01-10'  and '2020-01-31'
	Group by 	b.RootResellerId 
		,r.ResellerName
	order by DeviceCount desc

Open in new window


Although this is just a guess without sample data and expected results.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial