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
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
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, DeviceCountfrom(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.RootResellerIdorder by DeviceCount desc
select tblReseller.ResellerName, x.DeviceCountfromtblReseller 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.RootResellerIdorder by DeviceCount desc
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.