Avatar of websss
websss
Flag for Kenya asked on

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

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
arnold

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

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 Mandayam

Please post the full query you have written which is failing
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.