Grouping - 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

SQL Server Development (2000)

Grouping

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-04-09 : 05:41:16

How do I do grouping without having to put all the fields in the select clause that are in the group by clause?

I know there is a derived table for that but I'm not there yet. Can you gave me the example syntax?

I know you can do this: select x, group by x

but what about select x, y, z but group by y ? Any WHY NOT?????

The bracketed bit below runs just what I want but what if I want to get MORE fields out while grouping on just one field. That's the question.

Thanks

select ccs_quo_ref_number#2, Ccs_Quotes.Ccs_Quo_Company_Ref, ccs_quo_start_date
from (
select Ccs_Quotes.Ccs_Quo_Company_Ref
from ccs_quotes
where Ccs_Quotes.Ccs_Quo_Company_Ref like '%SWAL%'
group by Ccs_Quotes.Ccs_Quo_Company_Ref
)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-09 : 06:00:42

because when you do a group by you are returning just 1 row for that value where there could actually be many (with other values in other columns). so if you want to include other columns you need to decide how sql server should handle them. you are either grouping by them too so you get a distinct row per combination, or you want to 'aggregate' them in some way (just pick one of the values), something like min / max etc...

i suspect what you actually what to do in your case is use your derived table to ID which company_ref you're looking at, then join back to the original table to get the other columns. make sense?

Em

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-04-09 : 06:28:36

Whoa! Link to original table. I really was asleep. Haven't done this for a long time now. OK.

Would you just jot down the basic syntax? Not sure where the aliases go, or if you need them to start with, or what goes in which order...

I'm sure someone can set me straight here... it'll be kids' stuff I've got wrong here...

I'm thinking:

select ccs_quo_ref_number#2, Ccs_Quotes.Ccs_Quo_Company_Ref, ccs_quo_start_date
from ccs_quotes q
inner join (
select Ccs_Quotes.Ccs_Quo_Company_Ref
from ccs_quotes
where Ccs_Quotes.Ccs_Quo_Company_Ref like '%SWAL%'
group by Ccs_Quotes.Ccs_Quo_Company_Ref
) as company_ref_group
on q.Ccs_Quotes.Ccs_Quo_Company_Ref = company_ref_group.Ccs_Quotes.Ccs_Quo_Company_Ref

inside the bracket, all ran just fine, but am I joining on right field?

Errors for the whole:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'Ccs_Quotes' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'q.Ccs_Quotes' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'company_ref_group.Ccs_Quotes' does not match with a table name or alias name used in the query.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-09 : 06:42:23

i think you should start by just explaining what you want to acheive. forget the syntax for a minute.

Em

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-04-09 : 06:51:22

I want to group remotely, as in Crystal Reports, as described at the top of the topic, with x, y, z in SELECT but group by just one of those. That's ALL I want to do

I know it's possible, technically, not data-specific. I mean SELECT 1,000 fields, cut down the number of rows by grouping on just one field, maybe two, but I get syntax errors unless I match every single field in the SELECT and the GROUP BY.

I know all about aggregates and HAVING. But I want to do what I describe here

Thanks

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-09 : 06:58:31

sounds more like you just want to 'order by' rather than 'group'

quote:

I know all about aggregates and HAVING. But I want to do what I describe here

and i think i would suggest that if you don't understand why a column in your select needs to be included in you group by, then you don't know all about aggregates

Em

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-04-09 : 07:00:34

What is the difference between order by and group by functions, then? How would you summarise these?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-09 : 07:05:03

when something like cyrstal 'groups' data for you, it's not necessarily the same as a group by in sql server. it may just be ordered by a certain column and supressing the duplicate values

Em

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-04-09 : 07:31:08

Crystal has very clearly distinct functions for grouping and sorting

I know grouping is very data-oriented and depends on what's in your columns as to what you can group on and affects other columns in the table. Grouping on one column is never a problem for me. A second becomes a challenge. A third grouping column eludes me so far. I just know it's possible to nest a query with a grouping in there, and HAVING on that, then come out of there with a resultset with far less rows to worry about. Then you can group further in a seperate query. By then adjusting the HAVING parameters inside these queries, the outcomes can be fascinating - HAVING zero something, or >1, >2 and further...

But to pull loads more columns from a table (or multi-table-join) while grouping on just one or two is the goal

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-09 : 08:28:37

ok, so i've this very basic / generic example. run it and see it helps explain it better...


declare @table table (ref varchar(10), date datetime, col varchar(20))
insert into @table
			select 'ref 1','20080101','hello'
union all	select 'ref 1','20080102','hello again'
union all	select 'ref 2','20080104','ayup'

--this is the raw data
select * from @table

--this is pretty pointless, why not just do a select distinct ref?
select ref
from @table
group by ref

--so... group by ref, get max date for each ref
select ref, max(date)
from @table
group by ref

--to include col it's either got be in the group by or aggregate
select ref,col, max(date)
from @table
group by ref,col
--or
select ref,min(col), max(date)
from @table
group by ref

--in practice you wouldn't really want the min of col
--more likely you wan the col from the row with max date for each ref
--(getting confusing now)
select t1.*
from @table t1 
	join (	select ref, max(date) as date
			from @table
			group by ref
		  ) t2 on t2.ref = t1.ref and t1.date = t2.date

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-04-09 : 23:38:16

Are you confusing GROUP BY in SQL with Crystal Reports groups which, from memory, is a section or page break triggered by a value change? In which case you may not need GROUP BY in the SQL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources