'select' statement question again

[Mango]

Dear All,

Q1

table name = stk_tbl

prd_cde typ price
------- --- -----
P01 A 10
P02 A 10
P03 B 5
P04 B 5
P01 C 1
P02 C 1

we don't know how many prd_cde will present, we require add a group by statement,
and now what is the select statement to plus A AND B price
MINUS C prices and required group by prd_cde?




Q2

What is wrong of below SQL statement?
-----------
select sum(case when typ = 'C' then -pri else pri) from stk_tbl
---
but it come out an error statement("Function name is missing).").

[Adelle Hartley]

Mango <mong@mango.i-p.com> wrote in message news:3aa60845$1@news.devx.com...
>

> What is wrong of below SQL statement?
> -----------
> select sum(case when typ = 'C' then -pri else pri) from stk_tbl
> ---
> but it come out an error statement("Function name is missing).").

You didn't say which database system you are using.
For SQL Server, the syntax for CASE is:

CASE typ WHEN 'C' THEN -pri ELSE pri END

I don't know if you can put that into a SUM, so you should try
a simpler SELECT statement first:

SELECT typ, CASE typ WHEN 'C' THEN -pri ELSE pri END
FROM stk_tbl

If that works then try:

SELECT SUM(CASE typ WHEN 'C' THEN -pri ELSE pri END)
FROM stk_tbl

That should get you the result you want, but you should probably
think about whether you need an additional table:

typ sign
========
A 1
B 1
C -1

Hope that helps.

Adelle.
_____________________________________________________________
Impress your friends. Become an SQL guru.
http://www.sql-guru.com