Group By errors

Question

Post reply

Group By errors

Mark Harley

SSCrazy

Points: 2259
More actions
August 8, 2007 at 6:40 am

#174773

I am trying to write a stored procedure that returns over 10 rows from several different tables, but I am having problems with the group by. As I understand it, only the first 10 fields listed in the group by clause are used, but what does that mean when you have more than that listed in the select statement? Usually my group by mirrors my select, but that doesn't seem to work this time. Is there another way to do it?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

HI Mark,

Please could you post your select statement so that we could have a look at it for you.

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Mark Harley SSCrazy Points: 2259 More actions · Answer 2

Hi Chris,

This is essentially what I am attempting:

SELECTs.PartNumber, s.PartName, s.Date, s.Qty, s.Price, sr.Reason, pc.Description, psc.Description, s.SID, p.NonInventory, p.Return, p.Stock

FROMSales AS s, SaleReasons AS sr, PartClasses AS pc, PartSubClasses AS psc, Parts AS p, InventoryParts as ip, PartLocations as pl

WHEREs.SaleReasonID = sr.SaleReasonID AND

s.PartClassID = pc.PartClassID AND

s.PartSubClassID = psc.PartSubClassID AND

s.PartID = p.PartID AND

s.PartID = ip.PartID AND

s.BranchID = pl.BranchID

....

GROUP BY s.PartNumber,

s.PartName,

s.Date,

s.Qty,

s.Price,

sr.Reason,

pc.Description,

psc.Description,

s.SID,

p.NonInventory,

s.PartClassID,

s.PartSubClassID,

pl.PartLocationCode

I've abbreviated the WHERE clause because it is very long and convoluted, and I don't think that it is causing the problem.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

IS the problem with your result set or are you getting an actual error?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 4

Hi,

from BOL :-

"When GROUP BY is specified, either each column in any nonaggregate expression in the select list should be included in the GROUP BY list, or the GROUP BY expression must exactly match the select list expression."

you have some columns in the select that aren't in the group by, that aren't aggregate functions.

Is that the problem ?

Mark Harley SSCrazy Points: 2259 More actions · Answer 5

The problem is with the result set (assuming that my group by clause exactly mirrors my select clause). When the group by is so large, it seems like it ignores some of the fields. For example, I'm expecting all of the rows to be grouped by PartNumber, but that doesn't happen when I include the full list of fields. I read somewhere that this can happen when there are more than 10 fields in the group by clause, but I don't know that for a fact.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 6

HI Mark,

Just remember that the more fields that you are grouping by will normally mean more rows being returned because of the un-unqueness of the rows.

Does that make sense?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Mark Harley SSCrazy Points: 2259 More actions · Answer 7

I think so. So how are you supposed to manage the group by clause when you have to return so many rows? Is there another way to do it?

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 8

There are various ways.

For example try using subqueries(performance must be kept in mind here)

Also try using table Variables r temp tables to break down your results tehn join them at the end when you have you correct groups.

Does this make sense?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Mark Harley SSCrazy Points: 2259 More actions · Answer 9

Can you please give me an example involving temp tables?

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 10

That will be hard to do without know exactly what it is yu are trying to achieve through your query.

I'm sure the logic behind why you want to group by all the fields. For example what is your result set representing?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life