SQL assigning name to the SUM of a column

Question

SO i've been working on a series of sql commands / reports. I'm trying to get the SUM of total incidents that a particular individual creates then keeps adding to daily if incidents occur. So if Tim gets an incident 1 day, then 0 the next, then 2 the following day, it'll show his name next to 3 incidents.

Example: TableA

Tim | 1 | day one
Tim | 0 | day two
Tim | 2 | day three

TableB

Tim | 3 | week1

Here are some SQL commands I've tried:

INSERT INTO WeeklyReport(ImportDate, DayofData, [Name], TotalA, TotalB, TotalC)
SELECT GETDATE() AS ImportDate, DATEADD(day, -1, GETDATE()) AS DayofData, p.[Name], SUM(p.IncidentA), SUM(c.IncidentB), SUM(n.IncidentC)
FROM TableA p
INNER JOIN TableB c ON c.[Name] = p.[Name]
INNER JOIN TableC n ON n.[Name] = p.[Name]

I run into this error:

Msg 8120, Level 16, State 1, Line 2 Column 'TableA.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What changes can be made to the command to better accommodate this error?

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) — Tab Alleman, Jan 28 '16 at 19:06

score 2 · Accepted Answer · answered Jan 28 '16 at 16:48

2

Pretty basic thing, You need to add the non-aggregate column's in Group by when a Aggregate function is used in Select list

INSERT INTO weeklyreport 
            (importdate, 
             dayofdata, 
             [name], 
             totala, 
             totalb, 
             totalc) 
SELECT Getdate()                   AS ImportDate, --Constant so allowed
       Dateadd(day, -1, Getdate()) AS DayofData, --Constant so allowed
       p.[name],  -- Non Aggregate column should be present in Group by
       Sum(p.incidenta), 
       Sum(c.incidentb), 
       Sum(n.incidentc) 
FROM   tablea p 
       INNER JOIN tableb c 
               ON c.[name] = p.[name] 
       INNER JOIN tablec n 
               ON n.[name] = p.[name] 
GROUP  BY P.[name]

answered Jan 28 '16 at 16:48

Pரதீப்

91,748
19
131
172

this works, since my data is based on daily reports, if say day 1 incident is reported, next day same incident occurred, will it count both and just display "2"? – DHB Jan 28 '16 at 17:16
@Sam - Yes i think. Sample data and expected result will help us to understand better – Pரதீப் Jan 28 '16 at 17:20
sorry for delayed response thank you, I'm going to test this but for the most part this is answering my question. Thank you very much – DHB Jan 28 '16 at 19:38

SQL assigning name to the SUM of a column

1 Answers1