uksz uksz - 7 months ago 10
SQL Question

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?

Answer

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
Comments