Select records with group by clause ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

Select records with group by clause ?

Author

Topic

pareekfranksoul
Starting Member

26 Posts

Posted - 2008-07-23 : 08:17:41

please see the query:

select * from htcityref where ref_id = '9910690057' or u_code = '9910690057'

and results thrown by above query:

u_id name email phone u_code ref_id date

385 Raejendra NULL 9910690057 9910690057 NULL 2008-07-14 11:13:00
386 Sachin Kalabag NULL 9810036298 NULL 9910690057 2008-07-14 11:15:00
387 Deepan NULL 9818118060 NULL 9910690057 2008-07-14 11:15:00
388 Pooja NULL 9810004412 NULL 9910690057 2008-07-14 11:15:00
389 /siddarhi NULL 9958112222 NULL 9910690057 2008-07-14 11:17:00
1432 rah NULL 9810625988 NULL 9910690057 2008-07-21 13:07:00
1433 Anjali NULL 9810178898 NULL 9910690057 2008-07-21 13:07:00
1439 Vicky NULL 9811679010 NULL 9910690057 2008-07-21 13:11:00
1440 ankur NULL 9811679011 NULL 9910690057 2008-07-21 13:11:00
1441 Radhe NULL 9811679012 NULL 9910690057 2008-07-21 13:11:00
1442 Radhe NULL 9811679013 NULL 9910690057 2008-07-21 13:11:00

Brief:

all Persons phone numbers are referred by a person whose number is 9910690057 and name is Rajendra.

i want a query which return following fields:

name,ref_id,total(total phone numbers referred by that person)

i can get ref_id and total but not able to get name by below query:

select ref_id, count(phone) as total from htcityref group by ref_id order by total desc

Please help me ASAP

Thanks
Pankaj

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-07-23 : 08:32:55

So you are saying phone field is used as ref_id?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pareekfranksoul
Starting Member

26 Posts

Posted - 2008-07-23 : 08:53:39

No phone field is having unique numbers and ref_id field is having the number who has referred all those unique phone numbers.

In my result shown above:

name ref_id total(phone numbers referred)
Rajendra 9910690057 10

i want a query which return following fields:

name,ref_id,total(total phone numbers referred by that person)
as sampled above.

Thanks
Pankaj

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-23 : 10:12:41

hi check this

select name,ref_id,(select count(name) from htcityref where ref_id = '9910690057' )as total
from htcityref where ref_id = '9910690057'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 10:34:05

[code]SELECT h1.ref_id,h2.name,COUNT(*)
FROM htcityref h1
INNER JOIN htcityref h2
ON h2.phone=h1.ref_id
GROUP BY h1.ref_id,h2.name[/code]

pareekfranksoul
Starting Member

26 Posts

Posted - 2008-07-24 : 02:24:19

Thanks visakh16

Query works for me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources