Solved

Subquery Help!

Posted on 2016-09-08
9
73 Views
Last Modified: 2016-09-10
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
Comment
Question by:sj77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 41790183
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
 

Author Comment

by:sj77
ID: 41790203
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
 
LVL 41

Expert Comment

by:Sharath
ID: 41790211
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:sj77
ID: 41790334
resultset.jpg
This is what is output, which is a list of the calcs for each market.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 41790411
What is your expected output from the above result?
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41790637
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
 

Author Comment

by:sj77
ID: 41791201
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
 

Author Closing Comment

by:sj77
ID: 41791209
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41792277
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

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question