Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 91
  • Last Modified:

Subquery Help!

Hello Community -

I am a bit confused on this error and I need your help.

I run the following query and get the following error: Msg 8120, Level 16, State 1.
Column 'Data.avgGPM' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (Line 7)


SELECT


FORMAT(AVG(CAST(salesTotAvgMarket as NUMERIC(18,4))),'C','en-us') as [avgPerStoreSales], 
AVG(carCntTotAvgMarket) as [avgPerStoreCC],
FORMAT(AVG(CAST(salesTotAvgMarket as NUMERIC(18,4))) / AVG(carCntTotAvgMarket),'C','en-us') as [avgPerStoreARO],
[Data].[avgGPM]

FROM


	(select
		
	
	SUM(total-tax)  / COUNT(DISTINCT b.store_UIN) as [salesTotAvgMarket],
	--COUNT(DISTINCT b.shop_Market) as [totCntMarkets],
	--FORMAT(SUM(total-tax)  / COUNT(DISTINCT b.store_UIN), 'C','en-us') as [salesTotAvg,
	(COUNT(RO_NO) / COUNT(DISTINCT b.store_UIN)) as [carCntTotAvgMarket],
	FORMAT(((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS)) / (COUNT(RO_NO))), 'C', 'EN-US') as [avgARO],
	FORMAT((((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS)) - (SUM(P_COST + T_COST))) / (SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS))), 'P', 'EN-US') as [avgGPM]
	
	from hro as a
	inner join storeinfo as b
	on a.store_UIN = b.store_UIN
	
	where pay_date >= '07/31/2016' and pay_date <= '08/06/2016' and status IN ('C','Q')
	
	group by b.shop_Market
	) as Data

Open in new window


I then go and make the following changes to the query:

SELECT


FORMAT(AVG(CAST(salesTotAvgMarket as NUMERIC(18,4))),'C','en-us') as [avgPerStoreSales], 
AVG(carCntTotAvgMarket) as [avgPerStoreCC],
FORMAT(AVG(CAST(salesTotAvgMarket as NUMERIC(18,4))) / AVG(carCntTotAvgMarket),'C','en-us') as [avgPerStoreARO],
[Data].[avgGPM]

FROM


	(select
		
	
	SUM(total-tax)  / COUNT(DISTINCT b.store_UIN) as [salesTotAvgMarket],
	--COUNT(DISTINCT b.shop_Market) as [totCntMarkets],
	--FORMAT(SUM(total-tax)  / COUNT(DISTINCT b.store_UIN), 'C','en-us') as [salesTotAvg,
	(COUNT(RO_NO) / COUNT(DISTINCT b.store_UIN)) as [carCntTotAvgMarket],
	FORMAT(((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS)) / (COUNT(RO_NO))), 'C', 'EN-US') as [avgARO],
	FORMAT((((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS)) - (SUM(P_COST + T_COST))) / (SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS))), 'P', 'EN-US') as [avgGPM]
	
	from hro as a
	inner join storeinfo as b
	on a.store_UIN = b.store_UIN
	
	where pay_date >= '07/31/2016' and pay_date <= '08/06/2016' and status IN ('C','Q')
	
	group by b.shop_Market, [Data].[avgGPM]
	) as Data

Open in new window


I get the error: Msg 4104, Level 16, State 1.
The multi-part identifier "Data.avgGPM" could not be bound. (Line 28)


My dilemma is that if I put that [Data].[avgGPM] on the outside sub query it will group the data by markets and that is not what I am trying to do.

What I want to do is call in the outer select query the statement from [avgGPM] and do an average on it. See line below:

FORMAT((((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS)) - (SUM(P_COST + T_COST))) / (SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS))), 'P', 'EN-US') as [avgGPM]

Open in new window


Let me know if you need me to explain further.
0
sj77
Asked:
sj77
  • 4
  • 3
  • 2
1 Solution
 
SharathData EngineerCommented:
check this.
SELECT


FORMAT(AVG(CAST(salesTotAvgMarket as NUMERIC(18,4))),'C','en-us') as [avgPerStoreSales], 
AVG(carCntTotAvgMarket) as [avgPerStoreCC],
FORMAT(AVG(CAST(salesTotAvgMarket as NUMERIC(18,4))) / AVG(carCntTotAvgMarket),'C','en-us') as [avgPerStoreARO],
avgGPM

FROM


    (select
        
    
    SUM(total-tax)  / COUNT(DISTINCT b.store_UIN) as [salesTotAvgMarket],
    --COUNT(DISTINCT b.shop_Market) as [totCntMarkets],
    --FORMAT(SUM(total-tax)  / COUNT(DISTINCT b.store_UIN), 'C','en-us') as [salesTotAvg,
    (COUNT(RO_NO) / COUNT(DISTINCT b.store_UIN)) as [carCntTotAvgMarket],
    FORMAT(((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS)) / (COUNT(RO_NO))), 'C', 'EN-US') as [avgARO],
    FORMAT((((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS)) - (SUM(P_COST + T_COST))) / (SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS))), 'P', 'EN-US') as [avgGPM]
    
    from hro as a
    inner join storeinfo as b
    on a.store_UIN = b.store_UIN
    
    where pay_date >= '07/31/2016' and pay_date <= '08/06/2016' and status IN ('C','Q')
    
    group by b.shop_Market
    ) as Data
    group by avgGPM

Open in new window

0
 
sj77Author Commented:
It does work, however, not what I am looking for. Doing that gives me the GPM for all markets grouped where it should be doing a calc to find the average of GPM in the sub query.

TEST1 is the output of how you told me to run:
test1.jpg
TEST2 is the when I commented out your info and what my calcs are doing and what I want done similarly for avgGPM.
test2.jpg
0
 
SharathData EngineerCommented:
To understand it better, can you post the result of this query and your expected result.
select
        
    
    SUM(total-tax)  / COUNT(DISTINCT b.store_UIN) as [salesTotAvgMarket],
    --COUNT(DISTINCT b.shop_Market) as [totCntMarkets],
    --FORMAT(SUM(total-tax)  / COUNT(DISTINCT b.store_UIN), 'C','en-us') as [salesTotAvg,
    (COUNT(RO_NO) / COUNT(DISTINCT b.store_UIN)) as [carCntTotAvgMarket],
    FORMAT(((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS)) / (COUNT(RO_NO))), 'C', 'EN-US') as [avgARO],
    FORMAT((((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS)) - (SUM(P_COST + T_COST))) / (SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS))), 'P', 'EN-US') as [avgGPM]
    
    from hro as a
    inner join storeinfo as b
    on a.store_UIN = b.store_UIN
    
    where pay_date >= '07/31/2016' and pay_date <= '08/06/2016' and status IN ('C','Q')
    
    group by b.shop_Market

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sj77Author Commented:
resultset.jpg
This is what is output, which is a list of the calcs for each market.
0
 
SharathData EngineerCommented:
What is your expected output from the above result?
0
 
PortletPaulCommented:
I think you need another nesting level, like this:
SELECT
        AVG(CAST(salesTotAvgMarket AS numeric(18, 4)))                           AS [avgPerStoreSales]
      , AVG(carCntTotAvgMarket)                                                  AS [avgPerStoreCC]
      , AVG(CAST(salesTotAvgMarket AS numeric(18, 4))) / AVG(carCntTotAvgMarket) AS [avgPerStoreARO]
      , AVG([Data].[avgGPM]) --??
FROM (SELECT
                [salesstot] / [MarketCnt]  AS [salesTotAvgMarket]
              , [carCnt] / [MarketCnt]     AS [carCntTotAvgMarket]
              , [ARO] / [carCnt]           AS [avgARO]
              , ([GPM] - [pt_tot]) / [GPM] AS [avgGPM]
        FROM (SELECT
                        SUM(total - tax)                                                                                             AS [salesstot]
                      , COUNT(DISTINCT b.store_UIN)                                                                                  AS [MarketCnt]
                      , COUNT(RO_NO)                                                                                                 AS [carCnt]
                      , SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL - DISS) AS [ARO]
                      , SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL - DISS)            AS [GPM]
                      , SUM(P_COST + T_COST)                                                                                         AS [pt_tot]
                FROM hro AS a
                INNER JOIN storeinfo AS b ON a.store_UIN = b.store_UIN
                WHERE pay_date >= '20160731'
                AND pay_date <= '20160806'
                AND status IN ('C', 'Q')
                GROUP BY
                        b.shop_Market
            ) AS Gdata
    ) AS Data
;

Open in new window

Can I also suggest you avoid attempting any formats until you have the desired logic in place?
(It makes it easier to read)
1
 
sj77Author Commented:
BRAVO, Paul, BRAVO!

That did it. Thank you for helping me clarify the logic. Seems like you K.I.S.S kept it simple stupid and I did not do so. :)

I appreciate the golden nugget.
0
 
sj77Author Commented:
Not only was he able to provide the solution, but drew out the logic in such a way where it explains itself. Very informative answer!
0
 
PortletPaulCommented:
I forgot to ask in the question how to grab the SUM of [salesstot] so that it outputs basically the sum of the averages for each market?
Does this help?
SELECT
        AVG(CAST(salesTotAvgMarket AS numeric(18, 4)))                           AS [avgPerStoreSales]
      , AVG(carCntTotAvgMarket)                                                  AS [avgPerStoreCC]
      , AVG(CAST(salesTotAvgMarket AS numeric(18, 4))) / AVG(carCntTotAvgMarket) AS [avgPerStoreARO]
      , AVG([Data].[avgGPM]) --??
      , SUM([salesTotAvgMarket])       --  ??
FROM (SELECT
                [salesstot] / [MarketCnt]  AS [salesTotAvgMarket]

Open in new window

0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now