Sum of records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sum of records

Author

Topic

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-01-26 : 04:07:12

Dear all,
I need help from you,

I have a table named Messages with fields
Id (numeric)
Mobilenumber (varchar)
SmsBody (varchar)
MsgCnt (int)

Now here how to select all the records those have count of MsgCnt is greater than 10 for unique mobilenumber. It means in the table we can store number of records with same mobile number, but I need to see the records for unique mobilenumber where sum of MsgCnt is greater than 10

shaji

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-26 : 04:24:40

[code]SELECT Mobilenumber
FROM Messages
GROUP BY Mobilenumber
HAVING SUM(MsgCnt) > 10[/code]

KH
[spoiler]Time is always against us[/spoiler]

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-01-26 : 06:51:05

Thanks khtan,

Its working fine, But I need to select all records like select * from Messages GROUP BY Mobilenumber.........
How to do this?

Shaji

quote:
Originally posted by khtan

SELECT Mobilenumber
FROM Messages
GROUP BY Mobilenumber
HAVING SUM(MsgCnt) > 10

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-26 : 07:24:28

[code]SELECT m.*
FROM Messages m
INNER JOIN
(
SELECT Mobilenumber
FROM Messages
GROUP BY Mobilenumber
HAVING SUM(MsgCnt) > 10
) d ON m.Mobilenumber = d.Mobilenumber[/code]

KH
[spoiler]Time is always against us[/spoiler]

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-01-28 : 01:24:09

But the above query is fetching mobilenumber and its records repeatedly. But I need how to select the mobile number only one time?

shaji

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-28 : 03:21:03

quote:
Originally posted by shajimanjeri

But the above query is fetching mobilenumber and its records repeatedly. But I need how to select the mobile number only one time?

shaji

try with a DISTINCT

SELECT	DISTINCT m.*
FROM	Messages m
	INNER JOIN
	(
		SELECT Mobilenumber
		FROM   Messages
		GROUP BY Mobilenumber
		HAVING SUM(MsgCnt) > 10
	) d	ON	m.Mobilenumber	= d.Mobilenumber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources