CTE or Partition on group by ?
Hi
I have the following query
this outputs results like this:
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
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 descthis outputs results like this:
DeviceCount RootResellerId
3673 3071
2303 3449
1533 2990
1153 2975
1068 2299I 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
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 descselect 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
Please post the full query you have written which is failing
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.