Solved

Subquery Help!

Posted on 2016-09-08
9
43 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need Counts 11 43
MS SQL Backup 24 70
Group by and order by clause 28 36
Split Data in 1 column into 2 columns 8 21
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

747 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

9 Experts available now in Live!

Get 1:1 Help Now