CODEWITHSUNDEEP
sqlpostgresql
taga
taga

Reputation: 3895

Multiply two columns and show result in new column PostreSQL

I have a CSV file with more than 10 000 rows. I want to multiply two columns (quantity and unit price) and to show results in new column (I do not have that column in my table - column name can be "Total"). Also i need to calculate how much each customer spent. I wrote a code, but I get this error:

ERROR: integer out of range
SQL state: 22003

This is the code:

CREATE TABLE public."My_OnlineRetail"(invoiceno varchar(100),
                                        stockcode varchar(100),
                                        description varchar(100),
                                        quantity integer,
                                        invoicedate timestamp,
                                        unitprice float,
                                        currency varchar(10),
                                        customerid integer,
                                        country varchar(100));

SELECT * FROM public."My_OnlineRetail";
COPY public."My_OnlineRetail" FROM 'C:\Users\Jasmina\Desktop\OnlineRetail2NEW.csv' DELIMITER ',' CSV HEADER;

/*SELECT DISTINCT customerid FROM public."My_OnlineRetail";*/

SELECT customerid, quantity, unitprice, (quantity * quantity) FROM public."My_OnlineRetail";

I know that after this I need to loop trough every customer and sum the total column, but I do not know how to do that.

Upvotes: 1

Views: 1317

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I think you want:

SELECT customerid, SUM(quantity*unitprice) as total
FROM public."My_OnlineRetail"
GROUP BY customerid;

If you have a range problem, switch to numeric:

SELECT customerid, SUM( (quantity::numeric) * (unitprice::numeric) ) as total
FROM public."My_OnlineRetail"
GROUP BY customerid;

And for the final result:

SELECT customerid, SUM( (quantity::numeric) * (unitprice::numeric) )::numeric(15, 2) as total
FROM public."My_OnlineRetail"
GROUP BY customerid;

Upvotes: 3

Related Questions