|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2012-05-28 : 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 NumMenfrom ( select Husband_Id, COUNT(*) as NumTimesMarried from @Marriage group by Husband_Id having COUNT(*)>1) A |
 |
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2012-05-28 : 19:56:26
|
| Welcome...and do you mean the "A"? If so, no, it's an alias for the subquery. Without aliasing the subquery, you'd receive an error. |
|
|
query Question