Solved

Subquery Help!

Posted on 2016-09-08
9
50 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 40

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 40

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
 

Author Comment

by:sj77
ID: 41790334
resultset.jpg
This is what is output, which is a list of the calcs for each market.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 40

Expert Comment

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

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 48

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

914 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now