CODEWITHSUNDEEP
sql
ABK
ABK

Reputation: 511

Multiply two columns sql

I've created a view called marks containing marks grouped by city

+---------+----+
|city     |mark|
|---------|----|
|paris    |-1  |
|london   |-0.2|
|new york |-0.1|
+---------+----+

and I have another view factors which looks like this:

+---------+-------+
|factor1  |factor2|
|---------|-------|
|0.2      |0.5    |
+---------+-------+

And I want to multiply each mark in the first table by factor1 in order to get this:

+---------+--------+
|city     |new_mark|
|---------|--------|
|paris    |-1*0.2  |
|london   |-0.2*0.2|
|new york |-0.1*0.2|
+---------+--------+

I tried this:

SELECT 
    city, ((SELECT factor1 FROM factors) * mark * 1.0) AS new_mark
FROM 
    marks
GROUP BY
    1

but with no success, I get the following error:

ERROR: column "marks.mark" must appear in the GROUP BY clause or be used in an aggregate function

Can I get some help please?

Thanks in advance

Upvotes: 1

Views: 7327

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

I would put the subquery in the from clause and no group by is needed:

SELECT m.city, (f.factor1 * m.mark * 1.0) as new_mark
FROM marks m CROSS JOIN
     (SELECT factor1 FROM factors) f;

Upvotes: 2

Related Questions