SQL Server : can't get join to work when using sum

Question

When I run this sql:

SELECT 
    SUM(f.flex) as flex, 
    f.anv as anvid 
FROM 
    flex f 
GROUP BY 
    f.anv

I'll get:

 anvid   | flex
 ---------------
   2     |  120
   6     |  365 
   9     |  715 
  19     | 2485 
  20     | 1545 
  21     | 1833 
  22     |  796 
  35     | 1783 
  36     |  -15 
  37     |-1582 
  38     | 1999 
  59     |  510
  70     | -275

Now, I want to make a join on flex.anv and tablecolumn anv.id so that I'm able to print anv.anvnamn (username) instead of the id as above.

I've tried something like this:

SELECT 
    SUM(f.flex) as flex, 
    a.anvnamn as anvnamn 
FROM
    flex f 
INNER/LEFT/RIGHT JOIN 
    anv a ON f.anv=a.id 
GROUP BY 
    f.anv

Then I get an error:

Column 'anv.anvnamn' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Can anyone help me? Starting to get frustrated on this...

Thanks!

In your second query you changed the column in the select but didn't change it in the group by clause. If you want to select a.anvnamn, just change GROUP BY f.anv to GROUP BY a.anvnamn. — Caffé
– Caffé, Commented Oct 3, 2014 at 12:42

Gordon Linoff · Accepted Answer · 2014-10-03 12:40:09Z

1

So, put it in the group by clause:

SELECT sum(f.flex) as flex, a.anvnamn as anvnamn
from flex f JOIN
     anv a
     ON f.anv = a.id
GROUP BY a.anvnamn;

Note: if two ids have the same name, then they will be combined. If you don't want this behavior, then use:

GROUP BY a.anvnamn, f.anv;

answered Oct 3, 2014 at 12:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

j0kke Over a year ago

Thanks guys! I thought i tried that, but apparantly i didnt! :P

Jeffrey Wieder · Accepted Answer · 2014-10-03 12:39:53Z

1

Just add a.anvnamn to your GROUP BY Clause.

answered Oct 3, 2014 at 12:39

Jeffrey Wieder

2,3841 gold badge16 silver badges12 bronze badges

Collectives™ on Stack Overflow

SQL Server : can't get join to work when using sum

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related