Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pankaj_wolfhunter@yahoo.co.in - 29 Dec 2005 12:48 GMT

Greetings,
In sybase we have a function "COMPUTE BY" that gives
us the output with the grand total at the end.

like

select type, price, advance
from titles
where type like "%cook"
order by type
COMPUTE sum(price), sum(advance) BY type

type price advance
------------ ------------------------ ------------------------
trad_cook 119.92 8,000.00

Compute Result:
------------------------ ------------------------
119.92 8,000.00

The result gets a a total at the end for sum(price), sum(advance)

Do we have some functionality like this is DB2 or some workaround?

Help will be greatly appreciated

TIA

Reply to this Message

lennart@kommunicera.umea.se - 29 Dec 2005 13:52 GMT

> Greetings,
> In sybase we have a function "COMPUTE BY" that gives
[quoted text clipped - 19 lines]
>
> Do we have some functionality like this is DB2 or some workaround?

The standard way is:

select coalesce(type,'Total') as type, sum(price), sum(advance)
from titles
where type like "%cook"
group by grouping sets ((type),())
order by type

If not mistaken, type is a reserved word in sql so you should concider
changing it to car_type or whatever

HTH
/Lennart

Reply to this Message

ras9929 - 29 Dec 2005 14:50 GMT

Check out the DB2 SQL Reference 1 Manual for ROLLUP and GROUPING SETS.
These are some of the newer functions, and I have yet to use them in a
production application, but I believe they might meet your needs..

Reply to this Message

Knut Stolze - 29 Dec 2005 18:20 GMT

> If not mistaken, type is a reserved word in sql so you should concider
> changing it to car_type or whatever

Changing the column name is not necessary as (DB2) SQL is a
context-sensitive language and you can use keywords as
schema/table/column/funcgtion/... names. For example, the following is
valid SQL:

SELECT select select
FROM from.from from
WHERE where = where

(assuming the proper table definition.)

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

lennart@kommunicera.umea.se - 29 Dec 2005 21:39 GMT

> > If not mistaken, type is a reserved word in sql so you should concider
> > changing it to car_type or whatever
[quoted text clipped - 9 lines]
>
> (assuming the proper table definition.)

IMHO type is to vauge in most cases. But more important, using reserved
words makes porting to another dbms more difficult. But that is perhaps
nothing to worry about ;-)

/Lennart

[...]

Reply to this Message

Knut Stolze - 30 Dec 2005 07:25 GMT

>> > If not mistaken, type is a reserved word in sql so you should concider
>> > changing it to car_type or whatever
[quoted text clipped - 11 lines]
>
> IMHO type is to vauge in most cases.

You are right, but that is a completely different question.

> But more important, using reserved
> words makes porting to another dbms more difficult. But that is perhaps
> nothing to worry about ;-)

If those other systems also implement SQL, then it should not be a problem
at all. "Should"...

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

pankaj_wolfhunter@yahoo.co.in - 30 Dec 2005 10:16 GMT

thanks for all the replies. actually i am not able to find the manual
for ROLLUP and GROUPING SETS.
Can anyone tell me where to find it?

Reply to this Message

Knut Stolze - 30 Dec 2005 11:16 GMT

> thanks for all the replies. actually i am not able to find the manual
> for ROLLUP and GROUPING SETS.
> Can anyone tell me where to find it?

Not so easy to find, but here it is: http://tinyurl.com/7av8b

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

lennart@kommunicera.umea.se - 30 Dec 2005 11:20 GMT

> thanks for all the replies. actually i am not able to find the manual
> for ROLLUP and GROUPING SETS.
> Can anyone tell me where to find it?

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.
udb.doc/admin/r0000875.htm

scroll down to definition of "group-by-clause" or search for it.

Also, The DB2 SQL Cookbook (free for download) by Graeme Birchall

http://mysite.verizon.net/Graeme_Birchall/cookbook/DB2V82CK.PDF

contains examples

HTH
/Lennart

Reply to this Message

pankaj_wolfhunter@yahoo.co.in - 30 Dec 2005 12:35 GMT

thank again. and by the way, HAPPY NEW YEAR TO YOU. :-)

Reply to this Message

pankaj_wolfhunter@yahoo.co.in - 30 Dec 2005 12:35 GMT

thank again. and by the way, HAPPY NEW YEAR :-)

Reply to this Message