SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-27-2008, 05:41 AM
| ||||
| ||||
 Help 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      |
|
02-27-2008, 05:42 AM
| |||
| |||
| Re: Help pankaj_wolfhunter@yahoo.co.in wrote: > 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? > 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 |
|
02-27-2008, 05:42 AM
| |||
| |||
| Re: Help lennart@kommunicera.umea.se wrote: > 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.) -- Knut Stolze DB2 Information Integration Development IBM Germany |
|
02-27-2008, 05:42 AM
| |||
| |||
| Re: Help Knut Stolze wrote: > lennart@kommunicera.umea.se wrote: > > > 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.) > 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 [...] |
|
02-27-2008, 05:42 AM
| |||
| |||
| Re: Help lennart@kommunicera.umea.se wrote: > Knut Stolze wrote: >> lennart@kommunicera.umea.se wrote: >> >> > 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.) >> > > 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"... -- Knut Stolze DB2 Information Integration Development IBM Germany |
|
02-27-2008, 05:42 AM
| |||
| |||
| Re: Help pankaj_wolfhunter@yahoo.co.in wrote: > 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 -- Knut Stolze DB2 Information Integration Development IBM Germany |
|
02-27-2008, 05:42 AM
| |||
| |||
| Re: Help pankaj_wolfhunter@yahoo.co.in wrote: > 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/infoce...n/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_Bir...k/DB2V82CK.PDF contains examples HTH /Lennart |
Linear Mode
