Solved

Understanding the TSQL Group By clause

Posted on 2013-06-18
8
377 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 100 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 400 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 400 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

728 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