Solved

Understanding the TSQL Group By clause

Posted on 2013-06-18
8
374 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
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 48

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

839 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