locked
REQUIRE HELP IN WRITTING SQL QUERY RRS feed

  • Question

  • User-1296120440 posted

    Hi,

    Below is the table date on which we have to write a Query using SQL Server.

    Identify that we can see three records with same Transaction_No, CC-Code and currently we are using below Query to show individual records.

    select Transaction_No, 'CC-86' as CC_Code,'Bank Payments' as typeoftransaction,Transaction_No as RefNumber,Name as Name,Debit as Amount
    from BankBook where status='2' and paymenttype in('PF','Salary','Term Loan','Service Provider','General','Invoice Service','Advance','Transfer',
    'Supplier','Withdraw')

    Transaction_No CC_Code typeoftransaction RefNumber Name Amount
    2978359059 CC-86 Bank Payments 2978359059 Kotak Mahindra Bank Ltd. 34300
    6277065930 CC-86 Bank Payments 6277065930 Kotak Mahindra Bank Ltd. 5200
    9718231002 CC-86 Bank Payments 9718231002 Shree Shakti Weld Products  60831.28
    9718231002 CC-86 Bank Payments 9718231002 Shree Shakti Weld Products  9502.5
    9718231002 CC-86 Bank Payments 9718231002 Shree Shakti Weld Products  69666.22

    Now our requirement is to show all the transaction with same Transaction No, CC-Code as a single record and Amount column should reflect the Sum the Amount with the same Transaction_No and CC_Code.

    Expected Result Should be as shown Below:

    Transaction_No CC_Code typeoftransaction RefNumber Name Amount
    9718231002 CC-86 Bank Payments 9718231002 Shree Shakti Weld Products  139,999.5

    Could you please help me out?

    Regards,

    Vasu.

    Thursday, July 28, 2016 9:36 AM

Answers

  • User283571144 posted

    Hi vasu,

    vasu.dora@gmail.com

    Could you please help me out?

    As TechView says, you could use 'SELECT Transaction_No, CC_Code, SUM(Amount) AS Amount

    FROM BankDetails GROUP BY Transaction_no, CC_Code' query to get result.

    But the result is :

    Transaction_No                CC_Code              Amount

    9718231002                      CC-86                    139,999.5

    2978359059                      CC-86                    34300

    6277065930                       CC-86                    5200

    So I suggest you could do a little change:

    SELECT Transaction_No, CC_Code,typeoftransaction,RefNumber,Name,SUM(Amount) AS Amount
    FROM BankBook GROUP BY Transaction_no, CC_Code,typeoftransaction,RefNumber,Name
    

    The result is:

    Transaction_No  CC_Code    typeoftransaction     RefNumber          Name                                              Amount

    2978359059        CC-86           Bank Payments         2978359059       Kotak Mahindra Bank Ltd.         34300.000
    6277065930         CC-86          Bank Payments         6277065930        Kotak Mahindra Bank Ltd.         5200.000
    9718231002        CC-86          Bank Payments          9718231002        Shree Shakti Weld Products     140000.000

    Notice:

    After using group by query, we could only show groups columns' value.

    More details, you could refer to follow link:

    https://www.thunderstone.com/site/texisman/summarizing_values.html

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 1, 2016 9:19 AM

All replies

  • User-698989805 posted

    Hello Vasu! Never mind. It will be a single query as follows:

    SELECT Transaction_No, CC_Code, SUM(Amount) AS Amount
    FROM BankDetails GROUP BY Transaction_no, CC_Code

    Thursday, July 28, 2016 9:44 AM
  • User-1296120440 posted

    HI,

    Thank you for your immediate response.

    Query is focusing into finding the count of Students. My Requirement is to Sum up the Amount based on Combination of same Transaction No and CC_Code.

    Regards,

    GvrDora

    Thursday, July 28, 2016 10:03 AM
  • User-98329959 posted
    select Transaction_No, 'CC-86' as CC_Code,sum(Debit) as Amount
    from BankBook group by Transaction_no
    Thursday, July 28, 2016 12:58 PM
  • User283571144 posted

    Hi vasu,

    vasu.dora@gmail.com

    Could you please help me out?

    As TechView says, you could use 'SELECT Transaction_No, CC_Code, SUM(Amount) AS Amount

    FROM BankDetails GROUP BY Transaction_no, CC_Code' query to get result.

    But the result is :

    Transaction_No                CC_Code              Amount

    9718231002                      CC-86                    139,999.5

    2978359059                      CC-86                    34300

    6277065930                       CC-86                    5200

    So I suggest you could do a little change:

    SELECT Transaction_No, CC_Code,typeoftransaction,RefNumber,Name,SUM(Amount) AS Amount
    FROM BankBook GROUP BY Transaction_no, CC_Code,typeoftransaction,RefNumber,Name
    

    The result is:

    Transaction_No  CC_Code    typeoftransaction     RefNumber          Name                                              Amount

    2978359059        CC-86           Bank Payments         2978359059       Kotak Mahindra Bank Ltd.         34300.000
    6277065930         CC-86          Bank Payments         6277065930        Kotak Mahindra Bank Ltd.         5200.000
    9718231002        CC-86          Bank Payments          9718231002        Shree Shakti Weld Products     140000.000

    Notice:

    After using group by query, we could only show groups columns' value.

    More details, you could refer to follow link:

    https://www.thunderstone.com/site/texisman/summarizing_values.html

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 1, 2016 9:19 AM