CODEWITHSUNDEEP
sqlgroup-by
Victor
Victor

Reputation: 17077

Why group by requires all non aggregate columns

In sql, this query has invalid syntax:

select country,city, sum(income) from table1 group by country.

Because the group by expects country and city, not just country. But why is this the case? Because it is possible that I want the result to be grouped by country only. So in below table:

country   city            income

canada    toronto          100
canada    montreal          100
us        LA               200
us        NY               300

If I want to output:

country   city           sum( income)

canada    toronto          200
canada    montreal          200
us        LA               500
us        NY               500

What should be my group by clause?

Upvotes: 1

Views: 831

Answers (1)

Aducci
Aducci

Reputation: 26634

if you are using Sql Server you can do this

select 
  country,
  city, 
  sum(income) over (partition by country)
from 
  table1

Or if you are using another database you can use a sub query

  select 
      t1.country,
      t1.city, 
      (select sum(t2.income) from table1 t2 where t1.country = t2.country)
    from 
      table1 t1

Upvotes: 2

Related Questions