CODEWITHSUNDEEP
linqsql-server-2008sql-server-2005
Sweetie
Sweetie

Reputation: 1368

Logic of applying Group by in Sql Queries

I am asking a very beginner level of question but I am always confused whenever I want to use aggregate function with Group by. Actually I am getting the right results but I am not pretty sure about how group by is working here. My requirement is to get the count of sent items which is based on MessageGroup columns.

MessageId    SenderId      MessageGroup                             Message
_____________________________________________________________________________

1                2         67217969-e03d-41ec-863e-659ca26e660f     Hi
2                2         67217969-e03d-41ec-863e-659ca26e660f     Hello
3                2         67217969-e03d-41ec-863e-659ca26e660f     bye
4                1         c45dc414-9320-40a5-8f8f-9c960d6deffe     TC
5                1         8486d16b-294b-45a5-8674-e7024e55f39b     shutup

Actually I want to get the count for sent messages.here SenderId=2 has sent three messages to someone but I want to show a single count so I have used MessageGroup and I am doing Groupby and getting the count.

I have used Linq query::

 return DB.tblMessage.Where(m => m.SenderId == 2 ).GroupBy(m => m.MessageGroup).Count();

This returns "1" which is correct and I want to show (1) in sent messages.

But if I try to query the above in SQL Server, it returns 3

Here is my SQL query:

select count(*) 
from tblMessage 
where SenderId = 2 
group by MessageGroup

The Linq query is right As it returns me one as Microsoft says here

Actually I am confused with Group by. Please clear my point.

Upvotes: 0

Views: 51

Answers (2)

Sai Avinash
Sai Avinash

Reputation: 4753

When you are using GroupBy, which ever columns present in groupBy Clause should be in Select Clause

select MessageGroup,count(MessageGroup)from tblMessage 
where SenderId=2 
group by MessageGroup

Upvotes: 1

smoore4
smoore4

Reputation: 4866

You want to include MessageGroup as part of the select, like this:

select MessageGroup, count(*) 
from tblMessage 
where SenderId=2 
group by MessageGroup

Upvotes: 1

Related Questions