CODEWITHSUNDEEP
sqlpostgresql
uksz
uksz

Reputation: 18719

Issue with SUM of columns and group by

I have following columns in my table:

name | columnA | columnB

And I am trying to invoke the following query:

SELECT name, columnA + columnB AS price
FROM house
WHERE NOT (columnA IS NULL OR columnB IS NULL)
GROUP BY name
ORDER BY price

Which throws me:

house.columnA needs to be in GROUP BY clause. - I am not sure how I should understand that.

What I want to do, is to receive the table, where I will have name of house, and column price, which will equal to columnA + columnB, only if both of the columns are not null. And I would like to sort it by the calculated price.

Where am I doing a mistake?

Upvotes: 2

Views: 50

Answers (4)

iLikeMySql
iLikeMySql

Reputation: 746

i might be missunderstanding you, but i think what you need is a basic sql query without a group. also sum is a function that lets you sum values from different rows. creating the sum of values from the same row is trival.

this is how the aggregate function sum could be used to calculate the price for all houses per street:

select street, sum(columnA + columnB) as price_per_street
from houses
where columnA  is not null and columnB is not null
group by street
order by price;

this should be what you are looking for:

SELECT name, columnA + columnB AS price
FROM house
WHERE columnA IS NOT NULL AND columnB IS NOT NULL
ORDER BY price

Upvotes: 0

James
James

Reputation: 31

The error means you have to add columnA and also columnB in GROUP BY clause SELECT name, columnA + columnB AS price FROM house WHERE NOT (columnA IS NULL OR columnB IS NULL) GROUP BY name, columnA ORDER BY price

Upvotes: -1

sagi
sagi

Reputation: 40491

There are two options:

Option 1 - the group by is not needed. This will happen in case there is a single row for each name, in this case:

SELECT name,columnA+columnB as price
FROM house
WHERE columnA is not null 
 AND columnB is not null
ORDER BY price

Option 2 - the group by is needed, and that means you have more then 1 row for each name, and in this case you should use SUM :

SELECT name,sum(columnA+columnB) as price
FROM house
WHERE columnA is not null 
 AND columnB is not null
GROUP BY name
ORDER BY price

Upvotes: 2

rocks
rocks

Reputation: 190

Try this one

SELECT name, SUM(columnA + columnB) AS price
FROM house
WHERE NOT (columnA IS NULL OR columnB IS NULL)
GROUP BY name
ORDER BY price

Upvotes: 0

Related Questions