title: How can I order a selection by the results of a function (sum)?
body: <p>I have a table with a bunch of columns. Three columns are integers, and are labeled consumption, consumption2, and consumption3.</p>
  
  <p>I would like to select each full row of the table, but order the selection by the sum of the three consumption fields descending.</p>
  
  <p>I can order by each consumption column individually</p>
  
  <pre><code>order by consumption3 desc, consumption 2 desc, consumption desc
  </code></pre>
  
  <p>but I would prefer to sum these values and then order by that summed value.</p>
  
  <p>I can also write a 4GL program to do this, but am trying to solve this in SQL.</p>
  
  <p>If I do this, </p>
  
  <pre><code>select  *
  from    ws_bill
  order by sum(consumption) + sum(consumption2) + sum(consumption3)
  </code></pre>
  
  <p>then Informix's SQL wants every column in the <code>group by</code> list.</p>
  
  <p>Is there an easier way to do this, or should I just write the program?</p>
  
  <p>Versions of Informix SE/4GL running on Ubuntu 12.04</p>
  
  <pre><code>ics@steamboy:~/icsdev$ dbaccess -V
  DB-Access Version 7.25.UC6R1 
  Software Serial Number ACP#J267193
  ics@steamboy:~/icsdev$ fglpc -V
  
  Pcode Version 732-750
  
  Software Serial Number ACP#J267193
  ics@steamboy:~/icsdev$ 
  </code></pre>
  
  <p>Here is the table:</p>
  
  <pre><code>create table "ics".ws_bill 
    (
      yr char(2),
      bill_no integer,
      bill_month smallint,
      due_date date,
      tran_date date,
      prev_reading integer,
      curr_reading integer,
      consumption integer,
      consumption2 integer,
      consumption3 integer,
      water_charge money(10,2),
      sewer_charge money(10,2),
      other_charge decimal(10,2),
      curr_bal money(10,2),
      estimated char(1),
      prev_due_date date,
      time_billed datetime year to second,
      override char(1),
      curr_bal_save money(10,2)
    );
  revoke all on "ics".ws_bill from "public";
  
  create unique index "ics".ws_indx on "ics".ws_bill (bill_no,yr,
      bill_month);
  </code></pre>
  
  <hr>
  
  <p>This is the main cursor as denoted in the accepted answer for this post.</p>
  
  <pre><code>declare wb_cp cursor for
  select  b.yr,b.bill_no,
          sum(b.consumption + b.consumption2 + b.consumption3) as adj_tot
  into    cons_rec.* #defined record variable
  from    ws_bill b
  where   b.yr &gt;= start_yearG and b.yr &lt;= end_yearG
  group   by b.yr, b.bill_no
  order by adj_tot desc, b.yr desc, b.bill_no desc
  </code></pre>