Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

Re: (somewhat OT) Seeking a SQL counter-example

by extremely (Priest)
on Feb 28, 2007 at 21:26 UTC ( #602600=note: print w/ replies, xml ) Need Help??


in reply to (somewhat OT) Seeking a SQL counter-example

IDSalesIDCityIDMoney
110201000
210222000
311204000
411228000
510203000
610225000
711207000
811229000

select from table SalesID,CityID,Sum(Money) Groupby SalesID,CityID -- or select from table SalesID,CityID,Sum(Money) Groupby CityID, SalesID

Those should vary only in output order of the summed rows. The danger of automatic grouping is when/if someone picks "ID" in the select list... then it all goes to heck. The "common case" for Group By is to list all columns not aggregated in the select_column clause. Automatically forcing group-by seems to be almost assuredly doomed to run into keys and foreign-keys when they are incorrectly added to the columns list.

In your limited case, tho, I'm thinking you're safe.

--
$you = new YOU;
honk() if $you->love(perl)


Comment on Re: (somewhat OT) Seeking a SQL counter-example
Download Code
Re^2: (somewhat OT) Seeking a SQL counter-example
by dragonchild (Archbishop) on Feb 28, 2007 at 21:40 UTC
    Assuming that ordering is unimportant (because if ordering is important, you'll put an ORDER BY clause in), then does this mean that automagical grouping if there is a function that requires it (count, max, min, sum, etc) is bad or ok?

    My criteria for good software:
    1. Does it work?
    2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://602600]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (17)
As of 2015-07-01 18:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (16 votes), past polls