in reply to Re: Re: Array of Hash interface to an Array of Array?
in thread Array of Hash interface to an Array of Array?

My favourite SQL shortcoming is:

OK, at the risk of making a fool of myself, I'll bite.

I believe that that statement is syntactically incorrect. If it works in one engine, I would say it is broken. (Which one are we talking about, by the way)?

When you say sum(value) as value, in your order by clause, you can't use value (i.e. the right hand side of the selected item clause) as the aggregator. You must use sum(value) (the left hand side of the selected item clause).

I just whipped up a sample table in Postgresql. It chokes on your example, but renders the correct results on mine. Consider:

select * from foo;
 name | value 
------+-------
 foo  |   -10
 foo  |    -6
 foo  |    12
 foo  |    34
 bar  |     6
 bar  |    23
 bar  |    76
 rat  |    -6
 rat  |   -60
(9 rows)

[download]

and the result:

select name, sum(value) as value from foo group by name order by abs(s
+um(value)) desc;
 name | value 
------+-------
 bar  |   105
 rat  |   -66
 foo  |    30
(3 rows)

[download]

Looks good to me. Now, it may well be that you can use what I term "the right hand side" in group by, but I think I'll continue using the left hand side. Even if it is clunky and redundantly verbose, at least I know what's going on.

And I have been known to write Perl scripts that generate selected aggregates paired up with corresponding group by clauses just to ensure that it's all correct and avoid breaking the DRY principle.

Replies are listed 'Best First'.
Re: Re:x3 Array of Hash interface to an Array of Array? (grouping in SQL)
by jaa (Friar) on Sep 01, 2003 at 15:31 UTC
    8-)

    I like this SQL trap because I believe that it is natural to want to sort descending by an absolute aggregated value.

    The real issue is that the alias of the result column being the same as the name of an input column causes some SQL engines to deliver incorrect results without alerting the programmer to the hidden problem.

    Many SQL engines attempt to eliminate the sort phase by assembling the data in the correct order whilst examining / aggregating input rows. In most engines, a GROUP BY will automatically order a result set, but when present, the ORDER BY will take precedence. When the ORDER BY contains a function - e.g ABS() the function is called with the current input row value - not the aggregated resultset value which has not yet been finalised.

    Often, the results are only SOMETIMES incorrect - the nastiest form of bug. I tracked down an instance of this rare beetle, that was only seen on a handful of occasions in two years of processing.

    When I raised it with ****, the initial response was 'Why would you want to anyway?', followed by redirection to details of the SQL spec which indeed does not mention this facility.

    The following SQL at least complains if the SQL engine doesn't support sorting on an absolute aggregated value: 

      select 
    name, 
    sum(value) as sumval 
  from mytable
  group by name
  order by abs(sumval) desc

    [download]

    I prefer named resultset columns rather than 

      order by abs(2) desc

    [download]

    I haven't worked with Postgresql to know if it does the right thing - which 'value' column was summed, absed and sorted during the query? Changing the alias would show us if it is really doing the Right Thing - if so, hoorah! A second DB that caters to common sense!

    FWIW the DWIM DB engine that works in this case is probably long defunct - I haven't seen it for years - it was called FoxPro, and I think it was captured by the Big Bad Wolf.

[reply]
[d/l]
[select]
      FWIW the DWIM DB engine that works in this case is probably long defunct...
      I'm guessing FWIW is "for what it's worth", but what the heck is DWIM?

      Cheers!

[reply]

        From perlop:

        When presented with something that might have several different interpretations, Perl uses the DWIM (that's "Do What I Mean") principle to pick the most probable interpretation. This strategy is so successful that Perl programmers often do not suspect the ambivalence of what they write. But from time to time, Perl's notions differ substantially from what the author honestly meant.
[reply]

In Section Seekers of Perl Wisdom