• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

Understanding the TSQL Group By clause

My query returns multiple rows for the same SymbolID key value of 7206

SymbolName SecName           SymbolID  Curr5Day Prev5Day PctChg PVal  MaxClose  
OVRL      Overland Storage      7206      1.15      1.10      4.55      __u      1.19
OVRL      Overland Storage      7206      1.15      1.10      4.55      __u      1.19
OVRL      Overland Storage      7206      1.15      1.10      4.55      __u      1.19
OVRL      Overland Storage      7206      1.15      1.10      4.55      __u      1.19
OVRL      Overland Storage      7206      1.15      1.10      4.55      __u      1.19
OVRL      Overland Storage      7206      1.15      1.10      4.55      __u      1.19

I need to return only a single row.

SymbolName SecName           SymbolID  Curr5Day Prev5Day PctChg PVal  MaxClose  
OVRL      Overland Storage      7206      1.15      1.10      4.55      __u      1.19

This query is based on a 3 table join.  Exceptions-->Symbol-->StockHist

SELECT
  Exceptions.SymbolName
  ,SUBSTRING(Symbol.SecName, 1, 16) AS SecName
  ,Exceptions.SymbolID
  ,Curr5DayAvg
  ,Prev8DayAvg
  ,PctChg
  ,PVal
  ,(SELECT Max(ClosePrice) AS MaxClose FROM StockHist
      WHERE SymbolID= 7206 AND QuoteDate >= DATEADD(day,-65,GETDATE())
AND QuoteDate >= DATEADD(day,-65,GETDATE())) AS MaxClose  
  ,Exceptions.MarketID
  ,SiteURL
  ,PctUp
 FROM Exceptions INNER JOIN
 Symbol ON Exceptions.SymbolID = Symbol.SymbolID
 INNER JOIN StockHist ON Symbol.SymbolID = StockHist.SymbolID
 WHERE Exceptions.MarketID = 1 AND StockHist.SymbolID = 7206
 AND (Curr5DayAvg/Prev8DayAvg) BETWEEN 1 AND 15
 AND Prev8DayAvg >0 AND Curr5DayAvg BETWEEN 1 AND 15
 AND PVal = '__u'
 GROUP BY Exceptions.SymbolID
 ORDER BY PctChg DESC

Error:

Column 'Exceptions.SymbolName' is invalid in the select list
because it is not contained in either an aggregate function
or the GROUP BY clause.

This error occurs until I comment out the following columns:

Exceptions.SymbolName
  ,SUBSTRING(Symbol.SecName, 1, 16) AS SecName
  ,Exceptions.SymbolID
  ,Curr5DayAvg
  ,Prev8DayAvg
  ,PctChg
  ,PVal
  ,Exceptions.MarketID
  ,SiteURL
  ,PctUp

What am I missing?

Thanks
0
Dovberman
Asked:
Dovberman
4 Solutions
 
tim_csCommented:
Use Distinct instead of GroupBy as you aren't using an aggregate.
0
 
Simone BSenior E-Commerce AnalystCommented:
Although I can't see your data, I suspect that you will probably not need the subquery for your aggregate. You already have specified the SymbolID so all you need to do is add your QuoteDate filter to your primary WHERE clause.

When you use an aggregate such as MAX, any column you include in your select must be either part of the aggregate (such as you have with ClosePrice) or it has to be in the GROUP BY clause. You don't need to comment out the columns, just add them to the GROUP BY.

Please try this and let me know if it gives you the results you need.

SELECT 
  Exceptions.SymbolName
  ,Exceptions.SymbolID
  ,SUBSTRING(Symbol.SecName, 1, 16) AS SecName
  ,Curr5DayAvg
  ,Prev8DayAvg
  ,PctChg
  ,PVal
  ,Max(ClosePrice) AS MaxClose 
  ,Exceptions.MarketID 
  ,SiteURL
  ,PctUp
 FROM Exceptions INNER JOIN
 Symbol ON Exceptions.SymbolID = Symbol.SymbolID 
 INNER JOIN StockHist ON Symbol.SymbolID = StockHist.SymbolID
 WHERE Exceptions.MarketID = 1 AND StockHist.SymbolID = 7206 
 AND (Curr5DayAvg/Prev8DayAvg) BETWEEN 1 AND 15 
 AND Prev8DayAvg >0 AND Curr5DayAvg BETWEEN 1 AND 15 
 AND PVal = '__u' 
 AND QuoteDate >= DATEADD(day,-65,GETDATE())
 GROUP BY Exceptions.SymbolID
  ,Exceptions.SymbolName
  ,Symbol.SecName
  ,Curr5DayAvg
  ,Prev8DayAvg
  ,PctChg
  ,PVal  ,Exceptions.MarketID 
  ,SiteURL
  ,PctUp
 ORDER BY PctChg DESC 

Open in new window

0
 
Simone BSenior E-Commerce AnalystCommented:
One other thing that would be helpful, is if you indicate in your query which tables the columns are coming from. You have done so with Exceptions.SymbolID  ,Exceptions.SymbolName  ,Symbol.SecName  ,Exceptions.MarketID
but not with any of the others.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
DovbermanAuthor Commented:
I listed all columns in the Group By.

Would Distinct be more efficient?

What is the syntax for Distinct?

Thanks,
0
 
Simone BSenior E-Commerce AnalystCommented:
You use it right after the SELECT to get distinct rows:

SELECT DISTINCT
  Exceptions.SymbolName
  ,Exceptions.SymbolID
  ,SUBSTRING(Symbol.SecName, 1, 16) AS SecName
  ,Curr5DayAvg
  ,Prev8DayAvg
  ,PctChg
  ,PVal
  ,Max(ClosePrice) AS MaxClose 
  ,Exceptions.MarketID 
  ,SiteURL
  ,PctUp
 FROM Exceptions INNER JOIN
 Symbol ON Exceptions.SymbolID = Symbol.SymbolID 
 INNER JOIN StockHist ON Symbol.SymbolID = StockHist.SymbolID
 WHERE Exceptions.MarketID = 1 AND StockHist.SymbolID = 7206 
 AND (Curr5DayAvg/Prev8DayAvg) BETWEEN 1 AND 15 
 AND Prev8DayAvg >0 AND Curr5DayAvg BETWEEN 1 AND 15 
 AND PVal = '__u' 
 AND QuoteDate >= DATEADD(day,-65,GETDATE())
 GROUP BY Exceptions.SymbolID
  ,Exceptions.SymbolName
  ,Symbol.SecName
  ,Curr5DayAvg
  ,Prev8DayAvg
  ,PctChg
  ,PVal  ,Exceptions.MarketID 
  ,SiteURL
  ,PctUp
 ORDER BY PctChg DESC 

Open in new window

0
 
DovbermanAuthor Commented:
I used DISTNCT without the GROUP BY.

It was faster.

Thanks,
0
 
DovbermanAuthor Commented:
Thank you.
0
 
PortletPaulfreelancerCommented:
You do not (ever!) need to use BOTH distinct and group by in the same query.
Use one OR the other - not both.

IF, you really need to determine the maximum of close price, you will actually NEED group by, this CANNOT be achieved by using 'select distinct'

 ,Max(ClosePrice) AS MaxClose

if your data looked this:
SymbolName SecName           SymbolID  Curr5Day Prev5Day PctChg PVal  MaxClose  
OVRL      Overland Storage      7206      1.15      1.10      4.55      __u      1.19
OVRL      Overland Storage      7206      1.15      1.10      4.55      __u      1.22
OVRL      Overland Storage      7206      1.15      1.10      4.55      __u      1.23
OVRL      Overland Storage      7206      1.15      1.10      4.55      __u      1.24
OVRL      Overland Storage      7206      1.15      1.10      4.55      __u      1.25
OVRL      Overland Storage      7206      1.15      1.10      4.55      __u      1.26

select distinct would list all those rows!

using group by, and  ,Max(ClosePrice) AS MaxClose
only the last row would be listed

'select distinct' <> 'group by'

group by does more!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now