CODEWITHSUNDEEP
sql
dotsent12
dotsent12

Reputation: 137

Joining tables + SUM & GROUP BY function

I'm struggling in joining tables together with SUM and GROUP BY function. The query below works fine:

select ID, sum(amount)
from table1
group by ID
having sum(amount) between 1000 and 10000

As table1 only includes customer ID, I also need to join table CUSTOMERS, which contain customer name (column NAME). Following query will not work for me anymore:

select ID, name, sum(amount)
from table1
left join customers on table1.ID = customers.ID2
group by ID
having sum(amount) between 1000 and 10000

Ditching SUM and GROUP BY functionality does "fix" the issue as also column NAME will be available in the result, however I still need to sum and group the AMOUNT based on ID. How should I join the other table in this case to also present field NAME from table CUSTOMERS?

Column NAME or expression in SELECT list not valid'

is currently given as error message.

Upvotes: 0

Views: 489

Answers (3)

Vecchiasignora
Vecchiasignora

Reputation: 1315

try it

select t.ID, c.name, sum(t.amount)
from table1 t
left join customers c on table1.ID = customers.ID2
group by t.ID, c.name
having sum(t.amount) between 1000 and 10000

or without having depends on your requirement

Upvotes: 0

Avi
Avi

Reputation: 1145

Add "name" in group by clause

select table1.ID, customers.name, sum(table1.amount) amount
from table1,customers on table1.ID = customers.ID2
group by table1.ID,customers.name

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

It needs to be in the group by:

select t1.ID, c.name, sum(t1.amount)
from table1 t1 left join
     customers c
     on t1.ID = c.ID2
group by t1.ID, c.name;

Note the use of table aliases.

Upvotes: 0

Related Questions