Solved

Understanding the TSQL Group By clause

Posted on 2013-06-18
8
372 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
 

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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now