SQL, Too many values, Result consolidation

Question

I have a problem with an SQL query, in fact when i try to execute this i have this response : what i want to do is to calculate the sum and the count

Can you help me please ? thank you a lot

ORA-00913: too many values 00913. 00000 - "too many values" *Cause:
*Action: Erreur à la ligne 2, colonne 5

---

select distinct 
(select sum(che1.mon), count(che1.typ)
from awbeprod_fr.CHE_BKFRACOMC che1 
where che.ncp = che1.ncp and che.pie=che1.pie and che.typ in ('T','C')),

(select sum(che1.mon) 
from awbeprod_fr.CHE_BKFRACOMC che1 
where che.ncp = che1.ncp and che.typ = che1.typ and che.typ ='F'),

che.ncp, 
m.cpro, m.age,  m.ribdec,  m.dev,
p.lib, cli.cli, cli.pre, cli.nom, 
adcli.adr1, adcli.adr2, adcli.adr3, adcli.cpos, adcli.ville
from 
awbeprod_fr.CHE_BKFRACOMC che, awbeprod_fr.bkcom m, 
awbeprod_fr.bkprod p, awbeprod_fr.bkcli cli, 
awbeprod_fr.bkadcli adcli
where che.ncp = m.ncp 
and che.cli = m.cli and m.cli = cli.cli
and cli.cli = adcli.cli and m.cpro = p.cpro
and che.ncp = '11000003415'
order by che.ncp

---

Answer 1

If you use subquery you have to return one value not two or more. So just divide your first subquery to two:

select distinct 
(select sum(che1.mon)
from awbeprod_fr.CHE_BKFRACOMC che1 
where che.ncp = che1.ncp and che.pie=che1.pie and che.typ in ('T','C')),

(select count(che1.typ)
from awbeprod_fr.CHE_BKFRACOMC che1 
where che.ncp = che1.ncp and che.pie=che1.pie and che.typ in ('T','C')),

(select sum(che1.mon) 
from awbeprod_fr.CHE_BKFRACOMC che1 
where che.ncp = che1.ncp and che.typ = che1.typ and che.typ ='F'),

che.ncp, 
m.cpro, m.age,  m.ribdec,  m.dev,
p.lib, cli.cli, cli.pre, cli.nom, 
adcli.adr1, adcli.adr2, adcli.adr3, adcli.cpos, adcli.ville
from 
awbeprod_fr.CHE_BKFRACOMC che, awbeprod_fr.bkcom m, 
awbeprod_fr.bkprod p, awbeprod_fr.bkcli cli, 
awbeprod_fr.bkadcli adcli
where che.ncp = m.ncp 
and che.cli = m.cli and m.cli = cli.cli
and cli.cli = adcli.cli and m.cpro = p.cpro
and che.ncp = '11000003415'
order by che.ncp

Answer 2

Usage of ANSI JOIN would make your request clearer, and usage of GROUP BY should help you to do "all in one".

SELECT 
SUM (CASE WHEN che.typ in ('T', 'C') THEN che.mon ELSE 0 END) sumMonTC,
SUM (CASE WHEN che.typ in ('T', 'C') THEN 1       ELSE 0 END) cntMonTC,
SUM (CASE WHEN che.typ ='F'          THEN che.mon ELSE 0 END) sumMonF,
che.ncp, 
m.cpro, m.age,  m.ribdec,  m.dev,
p.lib, cli.cli, cli.pre, cli.nom, 
adcli.adr1, adcli.adr2, adcli.adr3, adcli.cpos, adcli.ville

FROM awbeprod_fr.CHE_BKFRACOMC che
    INNER JOIN awbeprod_fr.bkcom m ON che.ncp = m.ncp AND che.cli = m.cli
    INNER JOIN awbeprod_fr.bkprod p ON m.cpro = p.cpro
    INNER JOIN awbeprod_fr.bkcli cli ON m.cli = cli.cli
    INNER JOIN awbeprod_fr.bkadcli adcli on cli.cli = adcli.cli
WHERE che.ncp = '11000003415'
GROUP BY che.ncp, 
m.cpro, m.age,  m.ribdec,  m.dev,
p.lib, cli.cli, cli.pre, cli.nom, 
adcli.adr1, adcli.adr2, adcli.adr3, adcli.cpos, adcli.ville
ORDER BY che.ncp