Cannot group SQL results correctly

Hi i have a query which i need to show the number of transactions a user made,per day with the EUR equivalent of each transaction.

The query below does do that (find the eur equivalent by getting an average rate) but because the currencies are different i get the results by currency instead and not by total. what the query returns is:

Numb Transactions,Date, userid,transaction_type,total value (per currency),eur_equiv
1                  12/12, 2,     test              5                            10
2                   12/12,2,     test              2                             2

whereas i want it to return

Numb Transactions,Date, userid,transaction_type,total value (per currency),eur_equiv
1                  12/12, 2,     test              7                           12

the query is shown below

SELECT COUNT(DISTINCT(ot.ID)) AS 'TRANSACTION COUNTER'
      ,CONVERT(VARCHAR(10) ,ot.CREATED_ON ,103) AS [DD/MM/YYYY]
      ,lad.ci
      ,ot.TRA_TYPE
    ,c.C_CODE
      ,CASE 
            WHEN op.CURRENCY_ID='CURRENCY-002' THEN SUM(CAST(op.IT_AMOUNT AS MONEY)) 
                /(
                     SELECT AVG(CAST(cr.B_RATE AS MONEY)) AS AVG_RATE
                     FROM   C_RATE cr
                     WHERE  cr.CURRENCY_ID = 'CURRENCY-002'
                 )
            WHEN op.CURRENCY_ID='-CURRENCY-005' THEN SUM(CAST(op.IT_AMOUNT AS MONEY)) 
                /(
                     SELECT AVG(CAST(cr.B_RATE AS MONEY)) AS AVG_RATE
                     FROM   C_RATE cr
                     WHERE  cr.CURRENCY_ID = 'CURRENCY-005'
                 )
            WHEN op.CURRENCY_ID='CURRENCY-006' THEN SUM(CAST(op.IT_AMOUNT AS MONEY)) 
                /(
                     SELECT AVG(CAST(cr.B_RATE AS MONEY)) AS AVG_RATE
                     FROM   C_RATE cr
                     WHERE  cr.CURRENCY_ID = 'CURRENCY-006'
                 )
                           ELSE '0'
       END AS EUR_EQUIVAL
FROM   TRANSACTION ot
       INNER JOIN PAYMENT op
            ON  op.ID = ot.ID
       INNER JOIN CURRENCY c
            ON  op.CURRENCY_ID = c.ID
       INNER JOIN ACCOUNT a
            ON  a.ID = ot.ACCOUNT_ID
       INNE开发者_如何学JAVAR JOIN ACCOUNT_DETAIL lad
            ON  lad.A_NUMBER = a.A_NUMBER
       INNER JOIN CUST cus
            ON  lad.CI = cus.CI
WHERE  ot.TRA_TYPE_ID IN ('INBANK-TYPE'
                                 ,'IN-AC-TYPE'
                                 ,'DOM-TRANS-TYPE')
       AND ot.STATUS_ID = 'COMPLETED'
       AND cus.BRANCH IN ('123'
                                      ,'456'
                                      ,'789'
                                      ,'789')
GROUP BY
       lad.CI
      ,CONVERT(VARCHAR(10) ,ot.CREATED_ON ,103)
    ,c.C_CODE
      ,op.CURRENCY_ID
      ,ot.TRAN_TYPE_ID
HAVING SUM(CAST(op.IT_AMOUNT AS MONEY))>'250000.00'
ORDER BY
       CONVERT(VARCHAR(10) ,ot.CREATED_ON ,103) ASC

SELECT  MIN([Numb Transactions]
        , Date
        , UserID
        , Transaction_type
        , SUM([Total Value]
        , SUM([Eur Equiv]
FROM    (
          ... -- Your current select (without order by)
        ) q
GROUP BY
        Date
        , UserId
        , Transaction_type                

The problem resides most likely in a double row in your joins. What I do, is Select * first, and see what columns generate double rows. You might need to adjust a JOIN relationship for the double rows to disappear.

Without any resultsets, it's very hard to reproduce the error you are getting. Check for the following things:

• Select * returns only double rows on the data i will merge with an aggregate function. If the answer here is "NO", you will need to alter a JOIN relationship with a Subselect. I am thinking of that Account and Account detail table.
• certain joins can create duplicate rows if the join cannot be unique enough. Maybe you will have to join on multiple things here, for example JOIN table1 ON table1.ID = table2.EXT_ID and table1.Contact = table2.Contact

Couple of things:

1) Consider using a function for: SELECT AVG(CAST(cr.B_RATE AS MONEY)) AS AVG_RATE FROM C_RATE cr WHERE cr.CURRENCY_ID = 'CURRENCY-002' with the currencyID as a parameter

2) Would grouping sets work here?

3) was the sum on the case or on the individual whens?

 sum(CASE ..) vs sum(cast(op.IT_Amount as money)