CTE or Partition on group by ?

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

Microsoft SQL ServerSQL

Last Comment

slightwv (䄆 Netminder)

8/22/2022 - Mon

arnold

1/10/2020

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

1/10/2020

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