How can I order a selection by the results of a function (sum)?

Question

I have a table with a bunch of columns. Three columns are integers, and are labeled consumption, consumption2, and consumption3.

I would like to select each full row of the table, but order the selection by the sum of the three consumption fields descending.

I can order by each consumption column individually

order by consumption3 desc, consumption 2 desc, consumption desc

but I would prefer to sum these values and then order by that summed value.

I can also write a 4GL program to do this, but am trying to solve this in SQL.

If I do this,

select  *
from    ws_bill
order by sum(consumption) + sum(consumption2) + sum(consumption3)

then Informix's SQL wants every column in the group by list.

Is there an easier way to do this, or should I just write the program?

Versions of Informix SE/4GL running on Ubuntu 12.04

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$

Here is the table:

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);

This is the main cursor as denoted in the accepted answer for this post.

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 >= start_yearG and b.yr <= end_yearG
group   by b.yr, b.bill_no
order by adj_tot desc, b.yr desc, b.bill_no desc

Given that you are using Informix-SE (Standard Engine) rather than Informix (Dynamic Server), you will need to be aware that SE does not support many of the amenities of modern SQL, such as sub-queries in the FROM clause and ANSI-style JOIN operations. The work can be done, but if you ask questions here, make sure you mention Informix-SE because otherwise you may receive answers that will work on Informix 11.70 but not in Informix-SE 7.25. (Actually, you may get incorrect answers even so, but it will help those in the know to fix the problems.) — Jonathan Leffler, Jan 26 '13 at 00:52
You're right. I was not getting the correct count of top consuming accounts. As it turns out, I wound up writing a 4GL program sorting descending on the three consumption fields, and then using a continue foreach after the top 10 consuming accounts were found, until a break to the next fiscal year. — octopusgrabbus, Jan 26 '13 at 14:41

score 2 · Accepted Answer · edited Jan 26 '13 at 00:45

2

You are talking about summing three columns. This is a simple expression (consumption + consumption2 + consumption3). You do not need the sum(...) function unless you want to sum multiple rows that you are grouping together.

So, you need something along these lines:

select bill_no, bill_month, ..., (consumption + consumption2 + consumption3) as tot_sum
  from ws_bill
 order by tot_sum desc

edited Jan 26 '13 at 00:45

Jonathan Leffler

730,956
141
904
1,278

answered Jan 24 '13 at 18:38

Darius X.

2,886
4
24
51

score 0 · Answer 2 · answered Jan 24 '13 at 17:19

0

select  *, (sum(consumption) + sum(consumption2) + sum(consumption3)) as tot_sum
from ws_bill
order by tot_sum desc

answered Jan 24 '13 at 17:19

Rocky

327
1
9

1

Will eventually require all columns in group by list select *, (sum(consumption) + sum(consumption2) + sum(consumption3)) as tot_sum from ws_bill order by tot_sum desc #^ # 294: The column (yr) must be in the GROUP BY list. # I've added the table back into the original post. – octopusgrabbus Jan 24 '13 at 18:15

score 0 · Answer 3 · answered Jan 24 '13 at 17:34

0

What version of informix are you using? Assuming your table has a unique identifier and that your version of informx supports subqueries, then try:

SELECT *
FROM ws_bill w 
   JOIN (
      SELECT id, sum(consumption) + sum(consumption2) + sum(consumption3) as tot
      FROM ws_bill
      GROUP BY id
   ) w2 on w.id = w2.id
ORDER BY tot DESC

Good luck.

answered Jan 24 '13 at 17:34

sgeddes

62,311
6
61
83

Thanks. Here's the info you requested. I'll add it to the original post. The join threw a syntax error, but I'll keep looking. 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$ – octopusgrabbus Jan 24 '13 at 18:10

How can I order a selection by the results of a function (sum)?

3 Answers3