Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 491
  • Last Modified:

group within a group

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
anushahanna
Asked:
anushahanna
3 Solutions
 
HainKurtSr. System AnalystCommented:
try:

select count(1), PROG_KEY from TEACHER
group by PROG_KEY
having count(1) > 1
order by 1
0
 
ralmadaCommented:
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
 
lundnakCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
anushahannaAuthor Commented:
very nice- thanks.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now