Please help me to write this Query

  • 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

  • Can you show us the code you have so far ?



    Clear Sky SQL
    My Blog[/url]

  • 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

  • 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

  • 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