?
Solved

Understanding the TSQL Group By clause

Posted on 2013-06-18
8
Medium Priority
?
378 Views
Last Modified: 2013-06-18
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
Comment
Question by:Dovberman
[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
8 Comments
 
LVL 15

Assisted Solution

by:tim_cs
tim_cs earned 400 total points
ID: 39257276
Use Distinct instead of GroupBy as you aren't using an aggregate.
0
 
LVL 11

Accepted Solution

by:
Simone B earned 1600 total points
ID: 39257285
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
 
LVL 11

Assisted Solution

by:Simone B
Simone B earned 1600 total points
ID: 39257298
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:Dovberman
ID: 39257384
I listed all columns in the Group By.

Would Distinct be more efficient?

What is the syntax for Distinct?

Thanks,
0
 
LVL 11

Assisted Solution

by:Simone B
Simone B earned 1600 total points
ID: 39257390
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
 

Author Comment

by:Dovberman
ID: 39257924
I used DISTNCT without the GROUP BY.

It was faster.

Thanks,
0
 

Author Closing Comment

by:Dovberman
ID: 39257934
Thank you.
0
 
LVL 49

Expert Comment

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

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

741 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