|
flamblaster
Constraint Violating Yak Guru
USA
355 Posts |
 Posted - 05/28/2012 : 16:53:50
|
You might want to think about re-designing your tables to allow for a marriageid and have husbandid/wifeid as foreign keys from another table. That being said, I think you can accomplish what you're looking for by doing this:
declare @Marriage table (Husband_Id int, Wife_Id int, State char(2), City Varchar(30), MarriageDate date)
insert into @Marriage (Husband_id, Wife_Id,State, City, MarriageDate)
values
(1, 1, 'CO', 'Fort Collins', '2011-01-01'),
(1, 2, 'CO', 'Fort Collins', '2012-01-01'),
(2, 3, 'CO', 'Fort Collins', '2011-01-01'),
(3, 5, 'CO', 'Fort COllins', '2011-01-01'),
(3, 7, 'CO', 'Fort COllins', '2012-01-01')
EDIT:
If you want the total number of men that have been married twice or more, and not the particular men, you'd do this:
select COUNT(distinct husband_id) as NumMen
from (
select Husband_Id, COUNT(*) as NumTimesMarried
from @Marriage
group by Husband_Id
having COUNT(*)>1) A
|
Edited by - flamblaster on 05/28/2012 16:58:11 |
 |
|
query Question
