Solved

Group By, Aggrigate function ?

Posted on 2006-11-14
3
323 Views
Last Modified: 2012-06-21
Hi, I have written the SQL:

select * from vToneAudiogramCurve
group by Side having count(*) = 2

Then I get the error:
Column 'vToneAudiogramCurve.Client_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The reason I wan to groop it, and use the having count(') = 2 is that I only want to see results that is in 2 times. How can I get this to work ?
0
Comment
Question by:win32
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
ID: 17938449
select Site, count(*)
 from vToneAudiogramCurve
group by Side having count(*) = 2
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17938453
select * from vToneAudiogramCurve
where Side in ( select Site  from vToneAudiogramCurve group by Side having count(*) = 2 )
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17938464
you need to specify all the columns except those used for the aggregate functions, for eg, if you have columns Col1, col2 ...Coln
then

SELECT Col1,Col2
FROM urTable
GROUP BY Col1,Col2
Having Count(*) >1
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem with email format from SQL DB 4 34
CDC and AOG on MS SQL 2012 13 23
sql, case when & top 1 14 26
MS SQL + group by time 4 14
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

809 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