Solved

group within a group

Posted on 2011-02-14
5
485 Views
Last Modified: 2012-05-11
select count(*),PROG_KEY from TEACHER group by PROG_KEY order by 1
but the above should also make sure there are at least 2 TYPE_KEY for each PROG_KEY

how can you check that with t-sql?

thanks
0
Comment
Question by:anushahanna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 52

Expert Comment

by:Huseyin KAHRAMAN
ID: 34891499
try:

select count(1), PROG_KEY from TEACHER
group by PROG_KEY
having count(1) > 1
order by 1
0
 
LVL 41

Accepted Solution

by:
ralmada earned 250 total points
ID: 34892034
I was thinking something like this instead
a) 

SELECT PROG_KEY, count(1) from TEACHER
group by PROG_KEY
having count(distinct TYPE_KEY) > 1
order by 2

b) 
select 	PROG_KEY, 
	sum(sKey), 
from (
	select PROG_KEY, TYPE_KEY, count(*) as sKey 
	from TEACHER 
	group by PROG_KEY, TYPE_KEY
) a	
group by PROG_KEY 
having count(PROG_KEY) > 1
order by 2

Open in new window

0
 
LVL 7

Assisted Solution

by:lundnak
lundnak earned 125 total points
ID: 34892098
If you need two distinct type_key values per prog_key, then go with the first solution that ralmada recommended.

If they type_key's don't need to be distinct, than all you need to do is modify his solution by removing the distinct.

Ralmada provided a good solution.

SELECT PROG_KEY, count(1) from TEACHER
group by PROG_KEY
having count(TYPE_KEY) > 1
order by 2

Open in new window

0
 
LVL 52

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 125 total points
ID: 34892196
or you can use

select count(TYPE_KEY) cnt_type, PROG_KEY from TEACHER
group by PROG_KEY
having count(TYPE_KEY) > 1
order by 1

or

select count(distinct TYPE_KEY) cnt_type, PROG_KEY from TEACHER
group by PROG_KEY
having count(distinct TYPE_KEY) > 1
order by 1
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34897066
very nice- thanks.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question