Clever Geek Handbook

Add two lines to the query, but leave the rest as they are

If I have a request like

SELECT A, SUM(B)
FROM MyTable
WHERE ...
GROUP BY A

However, there are two values A

that I want to sum in the result. So let's say A

contains currency codes like:

'USD'
'USD big'
'EUR'
'GBP'
'JPY'
etc...

Now I want to sum the result SUM(B)

for each EXCEPT currency when A

- "USD"

or "USD big"

, then I want to add them together and multiply the row result "USB big"

by 100.

Is this possible in SQL Server?

To clarify, let's say the result of my query is currently:

A       | SUM(B)
------------
USD     | 1000
USD big | 2
EUR     | 50
GBP     | 26
JPY     | 5

I would like to modify my query so that it returns:

A       | SUM(B)
------------
USD     | 1200      -- i.e. 1000 + 2*100
EUR     | 50
GBP     | 26
JPY     | 5

Where it 1200

is 1000

out of line USD

, plus 100 times 2

of the line USD big

.

+3


source to share


2 answers


You can use an approach like this:



select
    T.A, sum(T.B)
from
    (
        select
            case when A = 'USD big' then 'USD' else A end as A,
            case when A = 'USD big' then B * 100 else B end as B
        from MyTable
        where ...
    ) as T
group by T.A
+5


source


The simplest approach is probably to combine two select statements, one

where CurrencyCode not in ('USD', 'USD big')

and the other

'where CurrencyCode in ('USD', 'USD big')'


or do it via one statement, something like the following should do the trick

 select CurrCode, Sum(CurrAmount)
 from (
        select case CurrencyCode   
                  when 'USD' then 'USD big'
                       else CurrencyCode
               end as CurrCode,
               case CurrencyCode
                  when 'USD big' then B* 100
                  else B      
               as CurrAmount
          from tableA
      ) T
group by CurrCode

Edit: Just noticed your requirement * 100. Apologies.

+2


source






More articles:


All Articles