急50分：求sql语句，我要计算supplier来料单数，但用group by 总是出错？

[mislrb]

我要统计supplier+receiver的数量，按supplier分组。


表结构如下：
PO Supplier Receiver Item Number Qty Return type
-----------------------------------------------------------------------
矱0100028 33138 RC020053 240-015000000-1 30,000.0 R?
砈0200020 33182 RC020492 532-0003-51 26.06
砈0200020 33182 RC020493 532-0003-51 26.06
砈0200025 33630 2010517 543-0115-4131 2,800.0 R?
砈0200025 33630 2010517 543-1085-4131 2,800.0 R?
砇0200152 33595 b010395 560-0146 600.0
砈0100034 33369 b010386 543-0171-25 20,000.0 R?
砈0100035 33369 RC021141 543-1129-25 20,000.0 R?
矼0200110 33189 RC021152 539-01300611-25 20,000.0 R?
矼0200056 33408 RC021200 544-0665 2,600.0 R?
矼0200018 33408 RC021202 544-0347 3,200.0 R?
砇0200126 33284 RC021245 560-0055 50.0 R?
砈0100035 33369 RC021281 543-1130-25 20,000.0 R?
砈0100034 33369 RC021282 543-0169-25 20,000.0 R?
砈0100034 33369 RC021282 543-0170-25 20,000.0 R?
砈0200060 33630 RC021355 543-6016-43 17,000.0 R?
砈0200053 33630 RC021356 543-6016-43 4,500.0 R?
...
...

thx.

[mislrb]

唉有毛病，现在group by 又行了，解决了。
select prh_vend,count(prh_vend||prh_receiver) as aa from qad.prh_hist group by prh_vend;

[thingsfly]

是你自己先前的sql语句写错了吧!!

[mislrb]

再请教decode函数如何用？
我想再在上面的一段SQL中加多一统计字段，统计prh_vend+prh_receiver中return type为'R？'的值。
thx.

[tomhuang]

DECODE(value,if1,then1[,if2,then2,]...,else)
例如：
select distinct city,decode(city,'san francisco','city by the bay',city) from comfort;
city decode(city,'sa
---------------------- ---------------
keene keene
san francisco city by the bay
查询return type为'R？'的值你只要在WHERE子句里加上return type='R?'不就行了吗??

[jlandzpa]

decode实现了if语句的功能.

[Hahahahahaha]

是不是要这个：
select prh_vend,count(prh_vend||prh_receiver),sum(decode(return_type,1,0)) as aa from qad.prh_hist group by prh_vend;

[snowy_howe]

select prh_vend,count(prh_vend||prh_receiver),sum(decode(return_type,'R?'1,0)) as aa from qad.prh_hist group by prh_vend;


U may try it