how does one accomplish the work done by the "compute" below

[landonmkelsey]

select rep,cust,amount
 from orders 
order by rep,cust 
compute sum(amount) by rep,cust 
compute sum(amount),avg(amount) by rep;

From a book by Groff and Weinberg "SQL" BTW a superb book!

I am sure the SQL shown works somewhere...probably Oracle.

Doesn't work with MySQL!

HMM! maybe I need to specify innodb!

[DJMaze]

Compute is MSSQL

There are many other ways like:

select
    rep,
    cust,
    sum(amount)
from orders 
group by rep, cust

Also found this for you: MySQL :: equivalent for 'compute' in sql server

[landonmkelsey]

thanks but the compute tokens produce more than this one:

select cust,rep,sum(amount) from orders group by rep,cust order by cust,rep;

I'd post it here but MySQL refuses!

I'll try MS SQL 2008!...later!

Thanks for the link!

[landonmkelsey]

appreciate the link...I just posted the same question on that forum

betting the answer has something to do with stored procedures

[landonmkelsey]

works great under MS SQL server:

results:

rep cust amount
----------- ----------- ----------------------
101 2102 3978

sum
----------------------
3978

rep cust amount
----------- ----------- ----------------------
101 2108 150

sum
----------------------
150

rep cust amount
----------- ----------- ----------------------
101 2113 22500

sum
----------------------
22500

sum avg
---------------------- ----------------------
26628 8876

rep cust amount
----------- ----------- ----------------------
102 2106 2130
102 2106 1896

sum
----------------------
4026

rep cust amount
----------- ----------- ----------------------
102 2114 15000

sum
----------------------
15000

rep cust amount
----------- ----------- ----------------------
102 2120 3750

sum
----------------------
3750

sum avg
---------------------- ----------------------
22776 5694

rep cust amount
----------- ----------- ----------------------
103 2111 600
103 2111 2100

sum
----------------------
2700

sum avg
---------------------- ----------------------
2700 1350

rep cust amount
----------- ----------- ----------------------
105 2103 3276
105 2103 702
105 2103 27500
105 2103 4104

sum
----------------------
35582

rep cust amount
----------- ----------- ----------------------
105 2111 3745

sum
----------------------
3745

sum avg
---------------------- ----------------------
39327 7865.4

rep cust amount
----------- ----------- ----------------------
106 2101 1458

sum
----------------------
1458

rep cust amount
----------- ----------- ----------------------
106 2117 31500

sum
----------------------
31500

sum avg
---------------------- ----------------------
32958 16479

rep cust amount
----------- ----------- ----------------------
107 2109 31350

sum
----------------------
31350

rep cust amount
----------- ----------- ----------------------
107 2124 2430
107 2124 652

sum
----------------------
3082

sum avg
---------------------- ----------------------
34432 11477.3333333333

rep cust amount
----------- ----------- ----------------------
108 2112 2925
108 2112 45000

sum
----------------------
47925

rep cust amount
----------- ----------- ----------------------
108 2114 7100

sum
----------------------
7100

rep cust amount
----------- ----------- ----------------------
108 2118 652
108 2118 760
108 2118 776
108 2118 1420

sum
----------------------
3608

sum avg
---------------------- ----------------------
58633 8376.14285714286

rep cust amount
----------- ----------- ----------------------
109 2108 1480
109 2108 5625

sum
----------------------
7105

sum avg
---------------------- ----------------------
7105 3552.5

rep cust amount
----------- ----------- ----------------------
110 2107 632
110 2107 22500

sum
----------------------
23132

sum avg
---------------------- ----------------------
23132 11566


(57 row(s) affected)

[landonmkelsey]

I do not believe in huge or complicated SQL statements.

I worked somewhere (Sierra Design in Reno) where there was a HUGE SQL command...pages and pages!

There was some small but effective bug (if you could call it that) concerning some sql code returning a NULL.

Talk about obfuscation and convoluted code!

Solution: use a stored procedue where the code is program not SQL!

Whew!

[DJMaze]

Doing the sum and avg can also be done outside the query in any programming language.
The only drawback is that you must order the data and check for rep and cust

int current_rep = 0, current_cust = 0, count_rep=0, count_cust=0, sum=0, avg=0;
while (query_result->row)
{
    if (current_cust != cust)
    {
        sum/count_cust;
        sum = count_cust = 0;
    }
    if (current_rep != rep)
    {
        avg/count_rep;
        avg = count_rep = 0;
    }
    avg += row.sum;
    sum += row.sum;
    ++count_rep;
    ++count_cust;
}