Sum Sql column with join

Question

I'm trying this code:

Select  C.CustomerNum
      , C.Coupon
      , C.name
      , C.Surname
      , Sum(P.Points)
From    customers C
Join    Points P
        On P.CustomerNum = C.CustomerNum
Where   C.Coupon = 'xxx-xxx-xxx-x';

I'm getting error:

Msg 8118, Level 16, State 1, Line 1 Column 'C.CustomerNum' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

What do you want SUM to base upon? The error is self-explanatory — techspider, Oct 10 '16 at 19:15
Possible duplicate of [Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause](http://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e) — techspider, Oct 10 '16 at 19:16

score 1 · Answer 1 · answered Oct 10 '16 at 19:25

You can also use.

SELECT C.CustomerNum,
       C.Coupon,
       C.name,
       C.Surname,
       P.Points
FROM   customers C
       INNER JOIN (SELECT Sum(Points) AS Points,
                                    CustomerNum
                   FROM   Points
                   GROUP  BY CustomerNum) P
         ON P.CustomerNum = C.CustomerNum
WHERE  C.Coupon = 'xxx-xxx-xxx-x';

To avoid having to add all the selected columns from customers to the GROUP BY list.

score 0 · Answer 2 · answered Oct 10 '16 at 19:16

The error message lays it out. You can do this (select only aggregates without a group by):

Select  Sum(P.Points)
From    customers C
Join    Points P
        On P.CustomerNum = C.CustomerNum
Where   C.Coupon = 'xxx-xxx-xxx-x';

or this (select aggregate and columns if columns are in group by):

Select  C.CustomerNum
      , Sum(P.Points)
From    customers C
Join    Points P
        On P.CustomerNum = C.CustomerNum
Where   C.Coupon = 'xxx-xxx-xxx-x'
group by c.customernum

but not this (select aggregates and columns without a group by):

Select  C.CustomerNum
      , C.Coupon
      , C.name
      , C.Surname
      , Sum(P.Points)
From    customers C
Join    Points P
        On P.CustomerNum = C.CustomerNum
Where   C.Coupon = 'xxx-xxx-xxx-x';

Doesnt work the second case neither i take same error message — user6927546, Oct 10 '16 at 19:19
I didn't add what your query should be. Just trying to help explain the error message. Check someone else's answer for a copy/paste solution. — Malk, Oct 10 '16 at 19:21

score 0 · Answer 3 · answered Oct 10 '16 at 19:17

When using an Aggregate function, you need to use a GROUP BY. Try this:

Select  C.CustomerNum
      , C.Coupon
      , C.name
      , C.Surname
      , Sum(P.Points)
From    customers C
Join    Points P
        On P.CustomerNum = C.CustomerNum
Where   C.Coupon = 'xxx-xxx-xxx-x'; 
GROUP BY C.CustomerNum
      , C.Coupon
      , C.name
      , C.Surname

Now, you'll probably want to narrow down what you actually want to select. For instance, it might be best to just group by C.Surname or C.CustomerNum

score 0 · Accepted Answer · answered Oct 10 '16 at 19:17

When you have an aggregate (SUM, COUNT, AVERAGE) you need to group your query by all the non-aggregate columns returned. For this:

Select  C.CustomerNum
      , C.Coupon
      , C.name
      , C.Surname
      , Sum(P.Points)
From    customers C
Join    Points P
        On P.CustomerNum = C.CustomerNum
Where   C.Coupon = 'xxx-xxx-xxx-x'; 
Group By C.CustomerNum
      , C.Coupon
      , C.name
      , C.Surname

score 0 · Answer 5 · answered Oct 10 '16 at 19:18

You can't use an aggregate function (in this case SUM()) with other non-aggregated columns without using a GROUP BY. However, you can do it by making this a windowed SUM() function:

Select  C.CustomerNum
      , C.Coupon
      , C.name
      , C.Surname
      , Sum(P.Points) Over (Partition By C.CustomerNum)
From    customers C
Join    Points P
        On P.CustomerNum = C.CustomerNum
Where   C.Coupon = 'xxx-xxx-xxx-x';

But perhaps you're wanting to group them by the CustomerNum, Coupon, Name, and Surname, in which case, you just need to add a GROUP BY:

Select  C.CustomerNum
      , C.Coupon
      , C.name
      , C.Surname
      , Sum(P.Points)
From    customers C
Join    Points P
        On P.CustomerNum = C.CustomerNum
Where   C.Coupon = 'xxx-xxx-xxx-x'
Group By C.CustomerNum, C.Coupon, C.Name, C.Surname

score 0 · Answer 6 · answered Oct 10 '16 at 19:30

@User6927546

If you are using mysql than it should work because i have cross checked same and its working fine.

select employee.empId ,employee.Name ,sum(w.workcode) from employee join works w on employee.workId=w.id where employee.Name='anoop'

I get the result set as : empId Name sum(w.workcode) 1 Anoop 200

Sum Sql column with join

6 Answers6