Nested Queries Help

[Sharkie21]

I'm trying to calculate the normalized average cost by area.

SELECT a.[market]
,sum(a.[cost]) as total_cost
,sum(a.[services]) as total_service
,(sum(a.[cost])/sum(a.[services]))/subquery.averagecost_national as Normalized_Average_Cost
FROM [Table A] a,
[Table B] b,
(SELECT
sum(a.[cost])/sum(a.[services]) as averagecost_national
FROM [Table A] a,
[Table B] b
where a.[ID] = b.[ID]) as subquery
where (a.[ID]) = b.[ID]
group by a.[market]

 

[Learning To Excel]

I'm not sure what you mean by normalized average, right now you are doing this (cost/service)/(cost/service) which will obviously = 1.
Here is a way for you to normalize the data from 0-1.

SELECT a.[market] 
,sum(a.[cost]) as total_cost
,sum(a.[services]) as total_service
,((a.[cost] - min(a.[cost])/(max(a.[cost] - min(a.cost])) as normalized_cost
,((a.[cost] - min(a.[service])/(max(a.[service] - min(a.service])) as normalized_service

FROM [Table A] a
INNER JOIN [Table B] b
on b.[ID] = a.[ID]

group by a.[market]

 

[Learning To Excel]

Looks like you would need an inline sql statement still. Something like below. Once again, I know this isn't normalized average, but maybe it will be something to get you started.

SELECT a.[market] 
,(a.[cost] - min(Inline.[inline_cost]))/(max(Inline.[inline_cost] - min(Inline.[inline_cost]) as normalized_cost
,(a.[service] - min(Inline.[inline_service]))/(max(Inline.[inline_service] - min(Inline.[inline_service])) as normalized_service

FROM [Table A] a
INNER JOIN [Table B] b
on b.[ID] = a.[ID],
(SELECT a.[cost] as inline_cost
,a.[services] as inline_service
FROM [Table A] a
INNER JOIN [Table B] b
on b.[ID] = a.[ID]) Inline

group by a.[market], a.[cost]