group by - 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

SQL Server 2005 Forums

Transact-SQL (2005)

group by

Author

Topic

vivo
Starting Member

24 Posts

Posted - 2010-03-22 : 05:17:05

select name,roomno from ...where...
group by name,roomno
having count(*) >=10

result :
Name RoomNo
AA 01
AA 03
BB 04

Do another select and process the records if name column in select = above name and roono column in select= above room no

Can anyone tell me how to relate these 2 ?. tks.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-22 : 05:20:07

sorry i am really not getting your requirement. pls explain.

Vaibhav T

vivo
Starting Member

24 Posts

Posted - 2010-03-22 : 05:27:24

We cannot select the cols which are not in group by clause , right ?
But I also need other cols for some processing.

So , my idea is to write a first select query with group by. I will get pairs of name and room no. Then write another select statement to reterive required cols where the name and room number is same as frist select.

tks.

quote:
Originally posted by vaibhavktiwari83

sorry i am really not getting your requirement. pls explain.

Vaibhav T

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-22 : 05:42:59

[code]
select *
from sometable t
inner join
(
select name, roomno
from sometable
group by name, roomno
) g on t.name = g.name and t.roomno = g.roomno
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-22 : 11:40:56

quote:

Originally posted by khtan


select *
from   sometable t
       inner join
       (
           select name, roomno
           from   sometable
           group by name, roomno
           having count(*) >=10
       ) g on t.name = g.name and t.roomno = g.roomno

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

as per OPs original reqmnt

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-22 : 12:00:44

thanks. I missed that

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-22 : 12:08:07

quote:
Originally posted by khtan

thanks. I missed that

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

np

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vivo
Starting Member

24 Posts

Posted - 2010-03-22 : 20:06:52

Thank you.

vivo
Starting Member

24 Posts

Posted - 2010-03-22 : 21:19:40

DECLARE db_cursor CURSOR FAST_FORWARD FOR
select *
from sometable t
inner join
(
select name, roomno
from sometable
group by name, roomno
having count(*) >=10
) g on t.name = g.name and t.roomno = g.roomno
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @NAME, @room_NO, @LVL,.....
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DETAILS=@DETAILS + @NAME + ' -- ' + @room_NO ' : ' + @LVL + char(10)
FETCH NEXT FROM db_cursor INTO @NAME, @room_NO, @LVL,.....
END
CLOSE db_cursor
DEALLOCATE db_cursor
variable declaration + exec other storedproc
end

select statement may result
morgan 123
morgan 456
sharon 123

@details may be
morgan --- 123 : lvl1
morgan --- 123 : lvl2
morgan --- 456 : lvl1
sharon --- 123 : lvl1

I want to prepare @DETAILS for each name/roomno pair and exec other proc for other transactions.
mean...
fetch cols related to morgan/123 first , exec proc
fetch cols related to morgan/456 first , exec proc
fetch cols related to sharton/123 first , exec proc

I think script is not correct based on req:
How shld it be ?

Tks alot.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-22 : 21:48:27

what is your requirement ?

What does "exec other storedproc" or "exec proc" is doing ?

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

vivo
Starting Member

24 Posts

Posted - 2010-03-22 : 21:58:14

To execute external stored procedure to send out email to executives.
Each mail content will be like

Schedule for Morgan in room 123
morgan --- 123 : lvl1
morgan --- 123 : lvl2

Thats why I need to execute SendMail stored procedure for each name/room pair.

tks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-22 : 22:03:53

then your exec proc should be inside the cursor loop not outside

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

vivo
Starting Member

24 Posts

Posted - 2010-03-22 : 22:09:47

If it is inside cursor, mail wll be send for each line , right ?
morgan --- 123 : lvl1
morgan --- 123 : lvl2
morgan --- 456 : lvl1
sharon --- 123 : lvl1
But I want to send mail for each name/room pair. Based on above , it shld send 3 seperated emails coz there are 3pairs of name/room.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-22 : 22:19:15

quote:
Originally posted by vivo

If it is inside cursor, mail wll be send for each line , right ?
morgan --- 123 : lvl1
morgan --- 123 : lvl2
morgan --- 456 : lvl1
sharon --- 123 : lvl1
But I want to send mail for each name/room pair. Based on above , it shld send 3 seperated emails coz there are 3pairs of name/room.

Yes. mail will be send for each record.

if for the above, you only want 3 email sent out, then change the query so that it will only return 3 records instead of 4

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

vivo
Starting Member

24 Posts

Posted - 2010-03-22 : 22:24:52

Can advise me ? I confuse coz it will send out mail for each record if it is inside.

quote:
Originally posted by khtan

quote:
Originally posted by vivo

If it is inside cursor, mail wll be send for each line , right ?
morgan --- 123 : lvl1
morgan --- 123 : lvl2
morgan --- 456 : lvl1
sharon --- 123 : lvl1
But I want to send mail for each name/room pair. Based on above , it shld send 3 seperated emails coz there are 3pairs of name/room.

Yes. mail will be send for each record.

if for the above, you only want 3 email sent out, then change the query so that it will only return 3 records instead of 4

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-22 : 22:30:00

pls post your table structure, sample data and the required result

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

vivo
Starting Member

24 Posts

Posted - 2010-03-22 : 22:40:55

A lot of tbls are related. So pls note that select statement for cursor may result
Name Room Level
A 01 12
A 01 13
B 02 12

Req:
Sent 2 seperated mails for each pair
One mail for
A 01 12
A 01 13
Another mail for
B 02 12

To send mail , call existing procedure 'SendMail'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-22 : 22:46:04

your query is returning 2 records for A - 01 because there are 2 different level (Level 12 & 13).

And for A - 01, you only want to send 1 mail. Which means you should be returning 1 record for A - 01. So which value of the Level do you want ?

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

vivo
Starting Member

24 Posts

Posted - 2010-03-22 : 22:55:27

For A - 01 pair, there may be multiple levels and multiple recepients. So I will send one emil for A-01 pair to multiple recepients.

Let say
A - 01 - level1 - executive1
A - 01 - level2 - executive2
Both executive1 and executive2 will receive same email.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-22 : 23:18:40

quote:
Originally posted by vivo

For A - 01 pair, there may be multiple levels and multiple recepients. So I will send one emil for A-01 pair to multiple recepients.

Let say
A - 01 - level1 - executive1
A - 01 - level2 - executive2
Both executive1 and executive2 will receive same email.

so for above, you want exec the send mail proc twice or just once with multiple recipient ?

or maybe you can return the result like
A - 01 - level1 - executive1, executive2

with the executive1 & 2 in comma separated value ?

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

vivo
Starting Member

24 Posts

Posted - 2010-03-22 : 23:23:20

Exec SendMail once for each name/room pair with multiple recipients. But I still need to extract recipient from each record , make it comma seperated and pass it to @Sendlist param of SendMail.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources