locked
Require Help In Writting a SQL SERVER QUERY RRS feed

  • Question

  • User-1296120440 posted

    Hi,

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

    Presently We have written a Query to show individual count of payments [Term Loan Payment, PF Payment,Bank Payment,Salary Payment]  at PM, CSK, CMC, HoAdmin, SAdmin Levels as shown below.

    Now the Requirement is changed and we have to show a single record with the total count of payment made at the below mentioned positons [PM, CSK, CMC, HoAdmin, SAdmin]

    typeoftransaction SrAccountant PM CSK CMC HoAdmin SAdmin CMD
    Term Loan Payment 0 0 0 0 0 2 0
    PF Payment 1 0 0 0 4 0 0
    Bank Payment 0 0 0 0 4 5 0
    Salary Payment 0 0 0 0 0 0 0

    Expected Result should be as shown as shown below:

    typeoftransaction SrAccountant PM CSK CMC HoAdmin SAdmin CMD
    BANK PAYMENTS 1 0 0 0 8 7 0

    Could you please help me out and let me know if you need any clarifications ?

    Reagrds,

    GvrDora.

    Wednesday, July 27, 2016 6:54 AM

Answers

  • User-1496088595 posted

    Considering the below result is from your view/table.

    typeoftransaction SrAccountant PM CSK CMC HoAdmin SAdmin CMD
    Term Loan Payment 0 0 0 0 0 2 0
    PF Payment 1 0 0 0 4 0 0
    Bank Payment 0 0 0 0 4 5 0
    Salary Payment 0 0 0 0 0 0 0

    then you can easily put this result in sub query and get your expected result.

    Suppose TempView is your view/table which returning the above  result.

    SELECT typeoftransaction,
                 SUM(SrAccountant) as SrAccountant,
                 SUM(PM) as PM,
                 SUM(CSK) as CSK,
                 SUM(CMC) as CMC,
                 SUM(HoAdmin) as HoAdmin,
                 SUM(SAdmin) as SAdmin,
                 SUM(CMD) as CMD
    FROM
            (
                select 'BANK PAYMENTS' as typeoftransaction, SrAccountant, PM, CSK, CMC, HoAdmin, SAdmin, CMD from TempView
            )
    GROUP BY typeoftransaction;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 27, 2016 8:57 AM