group by - 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

SQL Server 2000 Forums

Transact-SQL (2000)

group by

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-04-27 : 12:43:55

Is there a way around SQL Server's need to include all fields in the group by (except those with an aggregate).

Here is what I am trying to do:

Select * from Table A
group by A.1, A.2, A.3

Table A had 60 columns of data and I want it group by the three fields that I am identifying. SQL comes back and says that the other fields are not in the group by clause. Is there a way around this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-27 : 12:50:05

You can use a derived table. The derived table would be the query with the GROUP BY. You would JOIN to the derived table to get the other columns.

SELECT t1.Column1, t1.Column2, t1.Column3
FROM Table1 t1
INNER JOIN (SELECT Column1, MAX(Column2) FROM Table1 GROUP BY Column1) t2
ON t1.Column1 = t2.Column1 AND t1.Column2 = t2.Column2

Tara

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-27 : 12:50:48

The problem with the statement as you have presented it, is that GROUP BY is used for aggregation (counts, sums, averages, etc.) So SQL Server does not know WHICH value from the many within one group to show. Do you want the min? max? average? sum? count of rows?

Or are you thinking of GROUP BY like grouping in the Access Report Designer where it is really an ORDER BY and then within the display it allows you to do section or page breaks? If that's the case, then this is something to be handled by the client software (Crystal, SQL Reporting Services, etc.) and not within the SQL statement itself.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-04-27 : 13:09:16

I don't want to max , min, cunt or sum any of the columns. I simply want to group by those 3 columns. By performing a group y I will reduce my set of data from 200K records to 110K records.

Tara the example you provided is also doing a max on t2.column2. I take it there is no way around the aggregate to get a group by.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-27 : 13:10:19

Could you show us an example of your data and what it looks like after it would be grouped?

Tara

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-27 : 13:37:51

An example like Tara is requesting would be great. The question remains... once you have reduced your data set to half (110,000 records is still a LOT of rows for output) what values should be displayed? Do you have duplicates in your data? Are they EXACT duplicates (i.e. every value in every field is the same between the duplicates? If so, then you need to eliminate the duplicates and then look at restructuring your database to prevent duplicates in the future.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-04-27 : 13:49:58

Here is a small example. If I do a select * from Table A where column1='A0015' column2='A0037' and column3 ='20020001699'. I will get back 12 records. I wold like to add the group by on column4,column2,column5 so I only get back 1 record.

The result is: (12 records)
20040413 A0037 A A0015 20020001699 01 C 2002 04 2002 05 05010 A0037 XX90 A 1 1 1 1 1 1 1 1 1 1 0 0 0 0000 00 00 1 00000 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 NULL NULL
20040413 A0037 A A0015 20020001699 02 C 2002 04 2002 05 05010 XX86 A 1 1 1 1 1 1 1 1 1 1 0 0 0 0000 00 00 1 00000 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 NULL NULL
20040413 A0037 A A0015 20020001699 03 C 2002 04 2002 05 05010 DK75 A 1 1 1 1 1 1 1 1 1 1 0 0 0 0000 00 00 1 00000 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 NULL NULL
20040413 A0037 A A0015 20020001699 04 C 2002 04 2002 05 05010 DK78 A 1 1 1 1 1 1 1 1 1 1 0 0 0 0000 00 00 1 00000 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 NULL NULL
20040413 A0037 A A0015 20020001699 05 C 2002 04 2002 05 05010 W4019 A 1 1 1 1 1 1 1 1 1 1 0 0 0 0000 00 00 1 00000 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 NULL NULL
20040413 A0037 A A0015 20020001699 06 C 2002 04 2002 05 05010 A1972 A 1 1 1 1 1 1 1 1 1 1 0 0 0 0000 00 00 1 00000 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 NULL NULL
20040413 A0037 A A0015 20020001699 01 C 2002 04 2002 05 05010 A0037 XX90 A 1 1 1 1 1 1 1 1 1 1 0 0 0 0000 00 00 1 00000 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 NULL NULL
20040413 A0037 A A0015 20020001699 02 C 2002 04 2002 05 05010 XX86 A 1 1 1 1 1 1 1 1 1 1 0 0 0 0000 00 00 1 00000 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 NULL NULL
20040413 A0037 A A0015 20020001699 03 C 2002 04 2002 05 05010 DK75 A 1 1 1 1 1 1 1 1 1 1 0 0 0 0000 00 00 1 00000 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 NULL NULL
20040413 A0037 A A0015 20020001699 04 C 2002 04 2002 05 05010 DK78 A 1 1 1 1 1 1 1 1 1 1 0 0 0 0000 00 00 1 00000 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 NULL NULL
20040413 A0037 A A0015 20020001699 05 C 2002 04 2002 05 05010 W4019 A 1 1 1 1 1 1 1 1 1 1 0 0 0 0000 00 00 1 00000 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 NULL NULL
20040413 A0037 A A0015 20020001699 06 C 2002 04 2002 05 05010 A1972 A 1 1 1 1 1 1 1 1 1 1 0 0 0 0000 00 00 1 00000 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 NULL NULL

Table A has 3567839 records in it, if I select Column 4, Column2, Column5 from Table A and group by Column 4, Column2, Column5 then I get back 2080808 records.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-27 : 13:54:08

Which record would you want to get back though? Any record out of the 12?

Tara

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-04-27 : 14:06:13

Sorry.. I messed up, I would also want to group by Column6, therfore bringing back 6 records not 12. the top 6 and botttom six are identical records, so it doesn't matter which set come back.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-27 : 14:09:04

So then just use MAX or MIN if it doesn't matter which one to pull back.

If you need help with the query, please post table DDL (CREATE TABLE) and DML for sample data (INSERT INTO).

Tara

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-27 : 14:20:10

Or you might look into the use of DISTINCT instead of GROUP BY

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources