sql query case statement - SQL Server Forums

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.

jcarrallo
Starting Member

35 Posts

Posted - 2014-05-19 : 01:51:52

Thank you Nagino. It works very well.
Best

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources