individual and sum

Syed Ali Shah Rashdi
Syed Ali Shah Rashdi used Ask the Experts™
on
HI ,
 I have table with currency and values now i want to select data for some currency individual and for all other sum .

Table structure.

 Currency  , Spot , Interest, Forward.
   GBP        ,  1000  ,  50        ,  20
   USD       ,    3000  , 60       ,  10
   CHF       ,   5000   , 60          , 30
   AUD      , 2010    , 40           , 33
   CHF      ,  1000   , 10         ,  10

Expected result of query .

   Currecy , Spot , Interest , forward
       GBP        ,  1000,  50        ,  20
       USD       ,    3000, 60       ,  10
       Other    ,  8010 , 110 , 73

Thanks .
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
<total air code...>
SELECT Currency, Spot, Interest, Forward
FROM (
   SELECT Currency, Spot, Interest, Forward, 1 as SortOrder
   FROM YourTable
   WHERE Currency='GBP'
   UNION ALL
   SELECT Currency, Spot, Interest, Forward, 2 
   FROM YourTable
   WHERE Currency='USD'
   UNION ALL
   SELECT 'Other', Sum(Spot), Sum(Interest), Sum(Forward), 3
   FROM YourTable
   WHERE Currency NOT IN ('GBP', 'USD') ) a
ORDER BY SortOrder

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial