?
Solved

Subquery Help!

Posted on 2016-09-08
9
Medium Priority
?
78 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 2000 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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 the fundamental information of how to create a table.
Suggested Courses

765 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