May 24, 2010 at 10:20 am
Hi Guys,
I have two table name accrued_trans and account table
the first one has data like this
id amount quarter year opted_inmsisdn
111.3120101123451
26.2420091123451
415.7120101123452
63.7120101123452
741.2420091123456
813.9120101123457
916.2120101123458
109.3320090123454
and the other table look like
msisdnaccount_group
123451100
123452100
123453100
123454101
123455100
123456100
123457100
123458100
i have to write a query which will give select one by one account in same account group from account table and sum up the amount for that msisdn in accrued_trans table for that quarter and year.
for ex: 123451 it should get me the select sum(amount) from accrued_trans where msisdn = 123451 and quarter = 1 and year = 2010
then next for 123452, for all account in same account group.
If you still dint get what i am trying to say.. the result should look like below. I have to write a single query which does this.
msisdnamountyearquarter
12345111.320101
12345219.420101
12345713.920101
12345816.220101
Please help me, if you dint understand i am ready to explain it back on basis of your doubts.
Thanks in advance
May 24, 2010 at 10:25 am
Can you show us the code you have so far ?
May 24, 2010 at 10:25 am
The example you gave is pretty close to what you need, you just need to figure out how to JOIN the tables together.
Truthfully, I don't know that you are going to get an exact query from folks on this forum. Not when it looks like a homework assignment. However, if you prove you are trying to work on it and want to ask syntax or concept questions when you get stuck, you may get the assistance desired.;-)
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
May 24, 2010 at 1:21 pm
select
a.msisdn
,sum(b.amount) 'amount'
,b.[year]
,b.[quarter]
from
account a
inner join accrued_Trans b on
a.msisdn = b.msisdn
group by
a.acctGroup
,a.msisdn
,b.[year]
,b.[quarter]
order by
a.acctGroup
,[year]
,[quarter]
,a.msisdn
May 25, 2010 at 3:20 am
SELECT acc.*, t.total FROM account as acc
JOIN (SELECT tx.msisdn as msisdn, sum(tx.amount) as total FROM accrued_transaction tx WHERE tx.year = 2010 AND tx.quarter = 2 AND (tx.opted_in is null OR tx.opted_in = 1) GROUP BY tx.msisdn) t ON t.msisdn = acc.msisdn
WHERE acc.account_group = 300
and acc.opt_in_date is null
This is the way i have written with the help of google search and forum.. it gives me result, but it looks very complicated, can some one format this?
Becos i need to write the same in HQL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply