Return sum using aggregate function in SQL Server

Question

I have two tables.

Table1 has following data

Id1  Name   Comments
--------------------
 1   abc     hgdhg
 2   xyz     mnoph  
 3   ysdfr   jkljk
 4   asdf    iiuoo
 5   pqrs    liuoo

Table2 has following data

Id2   Id1   count      date
-------------------------------
 1     1      18     11/16/2005
 2     1      1     11/15/2005
 3     1      4     11/25/2005
 4     2      4     11/22/2005
 5     3      8     11/05/2005
 6     3      3     11/30/2005
 7     4      2     11/29/2005
 8     3      0     11/04/2005
 9     2      5     11/02/2005
 10    3      9     11/22/2005
 11    2      15     11/10/2005
 12    5      12     11/19/2005

I want to return output as name, comments, sum of all count since 11/10/2005

I am trying the following query(with out date where condition)

select 
    Name, Comments, sum(count) 
from 
    Table1 T1 
join 
    Table2 T2 on T1.Id1 = T2.Id1 
group by 
    ID1

But it is throwing error

Name 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 with query (with the date where condition)? What's wrong with this?

Thanks in advance

Thanks. This worked. can you help me in applying date condition? — Santosh
– Santosh, Commented Dec 17, 2017 at 0:14
You have to add a WHERE to filter by [date], btw do not use reserverd words to name object(in your case date column) date is a datatype. — Ilyes
– Ilyes, Commented Dec 17, 2017 at 0:31
Thanks much @Sami. Yeah, that is(date column) just for example — Santosh
– Santosh, Commented Dec 17, 2017 at 0:38

Ilyes · Accepted Answer · 2017-12-17 00:44:43Z

1

You have to add any columns not contained in the aggregate function, and use where to filter the results:

select Name, 
      Comments, 
      sum(count) 
from Table1 T1 join Table2 T2 on T1.Id1 = T2.Id1 
where T2.[date] >= '11/10/2005'
group by Name, Comments

answered Dec 17, 2017 at 0:44

Ilyes

15k5 gold badges31 silver badges61 bronze badges

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

Comments

Hasmukh Viradiya · Accepted Answer · 2017-12-17 05:29:39Z

1

you can use below query

SELECT  T1.Name ,
    T1.Comments ,
    SUM(T2.[count]) AS [count]
FROM    Table1 T1
    INNER JOIN Table2 T2 ON T1.Id1 = T2.Id1
WHERE   CAST(T2.[date] AS DATE) >= CAST('11/10/2005' AS DATE)
GROUP BY T1.Name ,
    T1.Comments

answered Dec 17, 2017 at 5:29

Hasmukh Viradiya

1748 bronze badges

1 Comment

marc_s Over a year ago

You should also explain what you've changed - and why - don't just dump a pile of code on us!

Julian Morales · Accepted Answer · 2017-12-19 00:41:56Z

1

Every column in a select statement without an aggregate function needs to be in the group by sentence too to prevent aggregate errors, about limiting the date, use where clause to define the condition, like shows ahead.

select 
    Name, Comments, sum(count) 
from 
    Table1 T1 
join 
    Table2 T2 on T1.Id1 = T2.Id1 
where  
    date >= '2005-11-10 00:00:00'
group by 
    Name, Comments

edited Dec 19, 2017 at 0:41

answered Dec 17, 2017 at 0:40

Julian Morales

364 bronze badges

1 Comment

marc_s Over a year ago

You should also explain what you've changed - and why - don't just dump a pile of code on us!

Collectives™ on Stack Overflow

Return sum using aggregate function in SQL Server

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related