SELECT with aggregate function in sql

Question

I have 2 tables:

CREATE TABLE cheque
  (
    ch_id          NUMBER NOT NULL ,
    ch_price       VARCHAR2 (100) ,
    ch_customer    NUMBER NOT NULL
  ) ;
ALTER TABLE cheque ADD CONSTRAINT check_PK PRIMARY KEY ( ch_id ) ;

CREATE TABLE customer
  (
    cus_id      NUMBER NOT NULL ,
    cus_name    VARCHAR2 (100) ,
  ) ;
ALTER TABLE customer ADD CONSTRAINT customer_PK PRIMARY KEY ( cus_id ) ;

ALTER TABLE cheque ADD CONSTRAINT check_customer_FK FOREIGN KEY ( ch_customer ) REFERENCES customer ( cus_id );

How to get SUM of prices in cheque for each customers? For example, if I had a customer John and 3 cheque records for him with ch_price 10, 20 and 30, I would have to get John - 60.

I tried this but it didn't work:

Select cus_name, sum(ch_price) 
from customer, cheque
where cheque_customer=cus_id;

You have tagged this question with the name of 3 different database products. Which are you actually using? — IMSoP, May 30 '14 at 08:47
It's ok if varchar2 value can be converted into number, if I'm not mistaken. — Green Fireman, May 30 '14 at 08:57
@user2246018 - when it can, maybe; but there is nothing stopping someone putting in something that cannot be converted, and then your queries will suddenly start failing. You should alway use the correct data type for the data that s going to be stored. Never store numbers or dates as strings. (I'd also recommend you use ANSI join syntax - if you are new to this, no point starting out with bad habits *8-) — Alex Poole, May 30 '14 at 09:04
"it didn't work" is not an error message. Please _always_ include the actual error message. If you'd have searched on it you'd have found your answer almost immediately... — Ben, May 30 '14 at 11:31

Raging Bull · Accepted Answer · 2014-05-30T08:51:48.100

3

Use GROUP BY:

Select cus_name, sum(ch_price) as Total
from customer, cheque
where cheque_customer=cus_id
group by cus_name

edited May 30 '14 at 08:51

answered May 30 '14 at 08:46

Raging Bull

18,593
13
50
55

SELECT with aggregate function in sql

1 Answers1