Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Unanswered: SQL Sum of count for 2 different rows in Access 2003

    Hi,

    I have the table in the atachment:

    What I like to do is a Query in order to count the tranactions type 101 having them grouped by Orderer_ID/Benef_ID (on the same column) and then sum up the results of the Transaction_Type column. All the transactions must be "Complete".

    In other words, in this exemple, the resulting query should have 2 columns and look like:

    Counterparty.............No_of_transactions
    Alpha.............................2
    Beta..............................3
    Gama.............................2
    Delta..............................1

    I managed to make an Union query, which took me pretty close to my result, but it looks like:

    Counterparty..............No_of_transactions
    Alpha...................................2
    Beta....................................1
    Beta....................................2
    Gama...................................1
    Gama...................................1
    Delta...................................1

    I could solve this by making another query based on this last one, by grouping the Counterparty and Summig up the No_of_transactions, but I would really need to do this in one query.

    Any help would be very much appreciated.

    Million thanks.
    Attached Thumbnails Attached Thumbnails Table.JPG  
    Last edited by mirtomi; 04-15-11 at 09:03.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    several points...

    first of all, why are there blanks in some of those columns?

    second, if you have a query that almost works, why wouldn't you show it?

    third, you actually can do it in one query, by putting the union query into a subquery in the FROM clause

    finally, you posted this in the ANSI SQL forum, and Access is pretty far from the ANSI standard -- wouldn't you rather have an Access solution?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    3
    Hello,
    Sorry for posting in the wrong section.
    There are blanks in the columns because for the 101 transaction type, for instance, there is no counterparty.
    The query that almost does the job is something like:

    Code:
    SELECT Transactions.BIC_Payer as BIC, Count(Transactions.Transaction_Type) AS [Total_transactions]
    FROM Transactions
    WHERE (((Tranzactions.Transaction_type)="NS") AND ((Transactions.Transaction_code)="101") AND ((Transaction.Status)="complete"))
    GROUP BY Transactions.BIC_payer
    HAVING (((Tranzactions.BIC_payer)<>""))
    UNION SELECT Transactions.BIC_Beneficiary as BIC, Count(Transactions.Transaction_Type) AS [Total_transactions]
    FROM Transactions
    WHERE (((Transactions.Transactions_type)="NS") AND ((Transactions.Transaction_code)="101") AND ((Transactions.Status)="complete"))
    GROUP BY Transactions.BIC_Beneficiary
    HAVING (((Tranzactions.BIC_Beneficiary)<>""));
    I kind of understand what you mean by putting the union query into the subquery, but honestly I'm a noob in SQL and don't really know how to modify the code above to get it right.

    Again, sorry for posting this in the wrong forum, you can move the thread or delete it if you consider, but any help would be very much appreciated before that.

    Many thanks.



    Quote Originally Posted by r937 View Post
    several points...

    first of all, why are there blanks in some of those columns?

    second, if you have a query that almost works, why wouldn't you show it?

    third, you actually can do it in one query, by putting the union query into a subquery in the FROM clause

    finally, you posted this in the ANSI SQL forum, and Access is pretty far from the ANSI standard -- wouldn't you rather have an Access solution?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT BIC
         , SUM(subtotal) AS [Total_transactions]
      FROM ( SELECT BIC_Payer as BIC
                  , COUNT(*) AS subtotal
               FROM Transactions
              WHERE Transaction_type = "NS"
                AND Transaction_code = "101"
                AND Status = "complete"
                AND BIC_payer <> ""
             GROUP 
                 BY BIC_payer
             UNION ALL
             SELECT BIC_Beneficiary as BIC
                  , COUNT(*) AS subtotal
               FROM Transactions
              WHERE Transaction_type = "NS"
                AND Transaction_code = "101"
                AND Status = "complete"
                AND BIC_Beneficiary <> ""
             GROUP 
                 BY BIC_Beneficiary ) AS u
    GROUP
        BY BIC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2011
    Posts
    3
    Oh my God it works like a charm! I realy can't thank you enough for this. You're a life saver!


Posting Permissions

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