Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sql query case statement

Author  Topic 

jcarrallo
Starting Member

35 Posts

Posted - 2014-05-18 : 14:06:43
Hi guys,

Please help me with this query.

The table is to keep commodity prices and the fields are as follows:

[SPH_DATE], [SPH_SYMBOL], [SPH_CLOSE]


The query below is to create two columns with the SPH_CLOSE (price of the commodity) for 'XAUEUR' and the other column for 'XAUUSD', and later I group by SPH_DATE


SELECT SPH_DATE,
SUM(Case SPH_SYMBOL when 'XAUEUR' then SPH_CLOSE ELSE 0 END) as columna_1,
SUM(Case SPH_SYMBOL when 'XAUUSD' then SPH_CLOSE ELSE 0 END) as columna_2

FROM RETORNOS_PCT

GROUP BY SPH_DATE
ORDER BY SPH_DATE


The result of the query is like this:

SPH_DATE|Columna_1| Columna_2
1996-01-01 00:00:00.000|1232|981,21
1996-01-02 00:00:00.000|1233|999,32
1996-01-03 00:00:00.000|1234|943,31
1996-01-04 00:00:00.000|1233|999,25

This is all fine except that I get lots of ceros for columna_1 and columna_2. This is because in this table there are other commodities with a longer history than 'XAUEUR' and 'XAUUSD' and for those dates I get 0. My question is: Can I filter the result so that I get only values >0?

Many thanks in advanced.
jay

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-05-18 : 21:01:02
Add where clause
WHERE SPH_SYMBOL IN ('XAUEUR', 'XAUUSD')


-------------------------------------
From Japan
Sorry, my English ability is limited.
Go to Top of Page

jcarrallo
Starting Member

35 Posts

Posted - 2014-05-19 : 01:51:52
Thank you Nagino. It works very well.
Best
Go to Top of Page
   

- Advertisement -