Toad World® Forums

How to SUM results of two rows into one


#1

select distinct c.cus_no, c.CU01L, a.ACCT_NO, ck.POS_DRS_CURR

from ck, acct a, cust c

and c.PRI_DC_REL >0

and a.serv = ‘DC’

and a.status =‘ACT’;

and result set
Cus# acct pos_dr_curr
23 xxxx 23
23 xxxx 48
28 xxx 17
34 xxxxx 14

here is my query:

where c.cus_no = ck.cus_no and c.cus_no = a.cus_noand ck.pos_drs_curr >= ‘1’

What im trying to do is sum the pos_dr_curr (23 and 48) and get one result (71) for the customer number 23. When i add the sum function in the select it gives me an error saying not a single group function. I been playing around with it and couldnt figure it out as of yet. Any help will be appreciated, thanks.

The result set i would like to get is:

23 xxxxxxxxxxx 71
28 xxxxxxxxx 17
34 xxxxxx 14

and so on…

Message was edited by: oercan_859

Message was edited by: oercan_859


#2

ON (EMP.DEPTNO = DEPT.DEPTNO)

FROM SCOTT.EMP EMP INNER JOIN SCOTT.DEPT DEPT

GROUP BY EMP.DEPTNO, DEPT.DNAME

Sum would be the correct function to use. When using an aggregrate function such as sum you must include all other columns in your group by.

IE:

SELECT EMP.DEPTNO, DEPT.DNAME, SUM (EMP.SAL)


#3

Ok thanks! I wasnt including then in the group by.