Hi friends,
i've a simple query like this that gives me the total of the currencies i.e(CAD,USD) of all the users.
select f.currency,sum(cast(f.balance as bigint)) as amount,f.id from Finance as f where f.currency in ('CAD','USD') group by f.currency,rollup(f.user_id) order by f.currency ASC.

I want no changes to my above query except I want to add one more field "name" from table "Names" and the join condition should be f.id = names.id.

Can someone please tell me how to accomplish this???Thanking you in advance.

How about posting the DDL for both tables? Thanks bud.

Hi Marcus,
Unfortunately i don't have access to the DDL's for both tables.All I know is the "id" links both these tables,and i need to add the "name" field to my existing Query.As,I am using roll up(first time),when i try to add the "name" field,all my data gets messed up.Right now I've something like this:

CURRENCY AMOUNT ID
CAD--------1000------
CAD---------500------1
CAD---------100------2
CAD---------400------3

USD---------500-------
USD---------100------1
USD---------200------2
USD---------200------3

This is exactly what i need,just want to add the "name" field after "id",can i???

CURRENCY AMOUNT ID NAME
CAD--------1000---------------
CAD---------500------1---XXX
CAD---------100------2---YYY
CAD---------400------3---ZZZ

USD---------500---------------
USD---------100------1---XXX
USD---------200------2---YYY
USD---------200------3---ZZZ

Thanks

OK, let's first format the SQL so we can read it (http://www.sqlinform.com/)

SELECT
f.currency,
sum(cast(f.balance AS bigint)) AS amount,
f.id
FROM Finance AS f
WHERE f.currency in ('CAD','USD')
GROUP BY f.currency,
rollup(f.user_id)
ORDER BY f.currency ASC.

The ORDER BY is probably not necessary since it is the first column in the GROUP BY. DB2 sorts the rows in order of the GROUP BY.

I am not an expert on ROLLUP, but did you try this:

SELECT
f.currency,
sum(cast(f.balance AS bigint)) AS amount,
f.id,
n.name
FROM Finance AS f,
Names AS n
WHERE f.currency in ('CAD','USD')
AND f.id = n.id
GROUP BY f.currency,
rollup(f.user_id, n.name)

Thanks Marcus,
Again Unfortunately i've tried that too and it didn't worked.I think i've to use some programming logic to get that "name" field.Anyways Thanks for your help.

Untested so it might need some tweaking:

select f.currency,
sum(cast(f.balance as bigint)) as amount,
f.id,
case when grouping(f.user_id) = 0
then (select name from names as n
where n.id = f.id)
else char(null)
end
from Finance as f
where f.currency in ('CAD','USD')
group by f.currency,
rollup(f.user_id)
order by f.currency ASC

You could try to concatenate the id and name column together with a space or dash between them:

Select SELECT
f.currency,
sum(cast(f.balance AS bigint)) AS amount,
f.id ||' '|| n.name