Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2012
    Posts
    15

    Unanswered: Select Top 1 in a Group by SQL QUERY?

    SELECT
    Tbl_Account.AccNo,
    SUM( case when TransType_ID = 0 then Amount else 0 end ) as Total_Adjustment,
    SUM( case when TransType_ID = 1 then Amount else 0 end ) as Total_Topup,
    SUM( case when TransType_ID = 2 then Amount else 0 end ) as Total_Comm,
    SUM( case when TransType_ID = 3 then Amount else 0 end ) as Total_Transfer,
    SUM( case when TransType_ID = 4 then Amount else 0 end ) as Total_RecvTransfer,
    SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='M' then Amount else 0 end ) as Total_Maxis,
    SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='D' then Amount else 0 end ) as Total_Digi,
    SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='C' then Amount else 0 end ) as Total_Celcom,
    (SELECT Top 1 Amount as Open_Balance)<<---how to do this at here?i want to get the first Amount as Opening Balance
    FROM Tbl_Account

    LEFT JOIN Tbl_Transaction ON Tbl_Account.AccNo = Tbl_Transaction.Transaction_AccNo AND (SELECT convert(Date,Tbl_Transaction.Transaction_Date, 105)) BETWEEN (SELECT convert(Date,'16-9-2012', 105)) AND (SELECT convert(Date,'30-9-2012', 105))
    LEFT JOIN Tbl_Topup ON Tbl_Transaction.Topup_ID=Tbl_Topup.Topup_ID
    Group By Tbl_Account.AccNo
    ORDER BY Tbl_Account.AccNo

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    how to do this at here?i want to get the first Amount as Opening Balance
    How are you defining "first"? Without knowing that, it's difficult to make a suggestion.

    Also, you've got elements of the WHERE clause in your JOIN statements, and you are left joining Tbl_Topup that is used in a manner to preclude it returning nulls, so you might as well inner join it instead.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Apr 2012
    Posts
    213
    Try something like that:

    Code:
    with CTE as
    (
        SELECT
            Transaction_AccNo,
            Amount,
            TransType_ID,
            Topup_ID,
            ROW_NUMBER() OVER(ORDER BY convert(Date,Transaction_Date, 105)) as RowNum
        from Tbl_Transaction
        where convert(Date,Transaction_Date, 105) BETWEEN '2012-09-16' AND '2012-09-30'
    )
    
    select
        Tbl_Account.AccNo,
        SUM( case when TransType_ID = 0 then Amount else 0 end ) as Total_Adjustment,
        SUM( case when TransType_ID = 1 then Amount else 0 end ) as Total_Topup,
        SUM( case when TransType_ID = 2 then Amount else 0 end ) as Total_Comm,
        SUM( case when TransType_ID = 3 then Amount else 0 end ) as Total_Transfer,
        SUM( case when TransType_ID = 4 then Amount else 0 end ) as Total_RecvTransfer,
        SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='M' then Amount else 0 end ) as Total_Maxis,
        SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='D' then Amount else 0 end ) as Total_Digi,
        SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='C' then Amount else 0 end ) as Total_Celcom,
        SUM( case when RowNum = 1 then Amount else 0 end) as Open_Balance
    FROM Tbl_Account
    LEFT JOIN CTE ON Tbl_Account.AccNo = CTE.Transaction_AccNo
    LEFT JOIN Tbl_Topup ON CTE.Topup_ID = Tbl_Topup.Topup_ID
    Group By Tbl_Account.AccNo
    ORDER BY Tbl_Account.AccNo
    Hope this helps.

  4. #4
    Join Date
    Aug 2012
    Posts
    15
    i mean select the top 1 Amount in the sql query

  5. #5
    Join Date
    Aug 2012
    Posts
    15
    Quote Originally Posted by imex View Post
    Try something like that:

    Code:
    with CTE as
    (
        SELECT
            Transaction_AccNo,
            Amount,
            TransType_ID,
            Topup_ID,
            ROW_NUMBER() OVER(ORDER BY convert(Date,Transaction_Date, 105)) as RowNum
        from Tbl_Transaction
        where convert(Date,Transaction_Date, 105) BETWEEN '2012-09-16' AND '2012-09-30'
    )
    
    select
        Tbl_Account.AccNo,
        SUM( case when TransType_ID = 0 then Amount else 0 end ) as Total_Adjustment,
        SUM( case when TransType_ID = 1 then Amount else 0 end ) as Total_Topup,
        SUM( case when TransType_ID = 2 then Amount else 0 end ) as Total_Comm,
        SUM( case when TransType_ID = 3 then Amount else 0 end ) as Total_Transfer,
        SUM( case when TransType_ID = 4 then Amount else 0 end ) as Total_RecvTransfer,
        SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='M' then Amount else 0 end ) as Total_Maxis,
        SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='D' then Amount else 0 end ) as Total_Digi,
        SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='C' then Amount else 0 end ) as Total_Celcom,
        SUM( case when RowNum = 1 then Amount else 0 end) as Open_Balance
    FROM Tbl_Account
    LEFT JOIN CTE ON Tbl_Account.AccNo = CTE.Transaction_AccNo
    LEFT JOIN Tbl_Topup ON CTE.Topup_ID = Tbl_Topup.Topup_ID
    Group By Tbl_Account.AccNo
    ORDER BY Tbl_Account.AccNo
    Hope this helps.
    Does not work, Open_Balance still is 0.00
    Last edited by chinye2020; 09-19-12 at 09:56.

  6. #6
    Join Date
    Aug 2012
    Posts
    15
    all Sum will according the TrasnType_ID and Acc No, so Result will show the all Acc No and their TransType Total Amount,
    and i just want in the Tbl_Transaction where is Top 1 Amount and show the Top 1 Amount as Opening_Balance, that's all

  7. #7
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
            ROW_NUMBER() OVER(PARTITION BY Transaction_AccNo
                              ORDER BY convert(Date,Transaction_Date, 105)) as RowNum
    Hope this helps.

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    You still have not defined what you mean by "top". Highest amount? Least amount? Earliest record? Latest record? In imex's sample code, the top record is defined as the first one in the table, which is apparently not what you want, yet still fulfils a reasonable definition of "top".

    It helps if you can define your requirements in plain language before translating them into SQL! It might also be useful if you were to post some example data from the tables, and then an example of the output that you require.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  9. #9
    Join Date
    Aug 2012
    Posts
    15
    Quote Originally Posted by imex View Post
    Try:

    Code:
            ROW_NUMBER() OVER(PARTITION BY Transaction_AccNo
                              ORDER BY convert(Date,Transaction_Date, 105)) as RowNum
    Hope this helps.
    Bravo!, thanks dude ! i will go to Google search what is this SQl meaning for ROW_NUMBER() and PARTITION BY

  10. #10
    Join Date
    Aug 2012
    Posts
    15
    this is select Top 1 Amount, How about i want to select the LAST Amount for Closing_Balance?

  11. #11
    Join Date
    Aug 2012
    Posts
    15
    with CTE as
    (
    SELECT
    Transaction_AccNo,
    Amount,Before_Amount,After_Amount,
    TransType_ID,
    Topup_ID,
    ROW_NUMBER() OVER(PARTITION BY Transaction_AccNo
    ORDER BY Transaction_Date) as RowNum,
    ROW_NUMBER() OVER(PARTITION BY Transaction_AccNo
    ORDER BY Transaction_Date DESC) as LastRowNum
    from Tbl_Transaction
    where convert(Date,Transaction_Date, 105) BETWEEN '2012-09-16' AND '2012-09-30'
    )

    select
    Tbl_Account.AccNo,
    SUM( case when TransType_ID = 0 then Amount else 0 end ) as Total_Adjustment,
    SUM( case when TransType_ID = 1 then Amount else 0 end ) as Total_Topup,
    SUM( case when TransType_ID = 2 then Amount else 0 end ) as Total_Comm,
    SUM( case when TransType_ID = 3 then Amount else 0 end ) as Total_Transfer,
    SUM( case when TransType_ID = 4 then Amount else 0 end ) as Total_RecvTransfer,
    SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='M' then Amount else 0 end ) as Total_Maxis,
    SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='D' then Amount else 0 end ) as Total_Digi,
    SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='C' then Amount else 0 end ) as Total_Celcom,
    SUM( case when RowNum = 1 then Before_Amount else 0 end) as Open_Balance,
    SUM( case when LastRowNum = 1 then After_Amount else 0 end) as Close_Balance
    FROM Tbl_Account
    LEFT JOIN CTE ON Tbl_Account.AccNo = CTE.Transaction_AccNo
    LEFT JOIN Tbl_Topup ON CTE.Topup_ID = Tbl_Topup.Topup_ID
    Group By Tbl_Account.AccNo
    ORDER BY Tbl_Account.AccNo

    DONE !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •