Simple Group needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple Group needed

Author

Topic

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-08-09 : 10:38:30

Hi all, got the following code which just needs to group by dbo.tblCOMStudy.[0020000D]

SELECT [0020000D], [00080018]
FROM dbo.tblCOMImage
INNER JOIN dbo.tblCOMSeries ON dbo.tblCOMImage._Id2 = dbo.tblCOMSeries.Id2
INNER JOIN dbo.tblCOMStudy ON dbo.tblCOMSeries._Id1 = dbo.tblCOMStudy.Id1
INNER JOIN dbo.tblFile ON dbo.tblCOMImage._idFile = dbo.tblFile.idFile
Where dbo.tblCOMStudy.[0020000D] in ('1.2.840.113619.2.55.3.2383071842.625.1292590480.81',
'1.2.124.113532.10.142.98.12.20070611.134228.514127',
'1.2.840.113619.2.55.3.2383071842.114.1269349892.866') --etc etc

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-09 : 10:54:01

Did you post prematurely before completing the question you were going to ask? If all you need to do is group by, then you can add that at the very end of the query you posted:

SELECT
	[0020000D],
	[00080018]
FROM
	dbo.tblCOMImage
	INNER JOIN dbo.tblCOMSeries
		ON  dbo.tblCOMImage._Id2 = dbo.tblCOMSeries.Id2
	INNER JOIN dbo.tblCOMStudy
		ON  dbo.tblCOMSeries._Id1 = dbo.tblCOMStudy.Id1
	INNER JOIN dbo.tblFile
		ON  dbo.tblCOMImage._idFile = dbo.tblFile.idFile
WHERE
	dbo.tblCOMStudy.[0020000D] IN (
	                              '1.2.840.113619.2.55.3.2383071842.625.1292590480.81', 
	                              '1.2.124.113532.10.142.98.12.20070611.134228.514127', 
	                              '1.2.840.113619.2.55.3.2383071842.114.1269349892.866') 
GROUP BY
	dbo.tblCOMStudy.[0020000D]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-09 : 11:26:18

quote:

Originally posted by sunitabeck

Did you post prematurely before completing the question you were going to ask? If all you need to do is group by, then you can add that at the very end of the query you posted:

SELECT
	[0020000D],
	{MIN/MAX/SUM/..}([00080018])
FROM
	dbo.tblCOMImage
	INNER JOIN dbo.tblCOMSeries
		ON  dbo.tblCOMImage._Id2 = dbo.tblCOMSeries.Id2
	INNER JOIN dbo.tblCOMStudy
		ON  dbo.tblCOMSeries._Id1 = dbo.tblCOMStudy.Id1
	INNER JOIN dbo.tblFile
		ON  dbo.tblCOMImage._idFile = dbo.tblFile.idFile
WHERE
	dbo.tblCOMStudy.[0020000D] IN (
	                              '1.2.840.113619.2.55.3.2383071842.625.1292590480.81', 
	                              '1.2.124.113532.10.142.98.12.20070611.134228.514127', 
	                              '1.2.840.113619.2.55.3.2383071842.114.1269349892.866') 
GROUP BY
	dbo.tblCOMStudy.[0020000D]

to OP
when you apply GROUP BY on a field you need to apply aggregates over other fields as there will be more than one value associated within a group. so add an aggregate function like SUM,MIN,MAX etc as shown

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources