November 15, 2006 at 9:01 pm
I made up this example to try and simplify a couple queries I needed. Query 1 should be ok, but #2 and #3 probably could be a single query without using a temporary table. I'm trying to learn the right way to do these things. Thanks.
TABLE: FamilyTable
Family ChildAge
Jones 1
Jones 2
Jones 9
Smith 4
Thompson 4
Gorgon 5
Watson 3
Watson 1
Watson 14
Carter 3
Carter 8
Carter 9
-----------------------------------------------------------
Query 1:
SELECT ChildAge, Count(*) as CountIt
FROM FamilyTable
GROUP BY ChildAge
ORDER BYT ChildAge
ChildAge CountIt
1 2
2 1
3 2
4 2
5 1
8 1
9 2
14 1
-----------------------------------------------------------
Query 2:
SELECT Family, COUNT(*) as CountIt
INTO #TempFamily
FROM FamilyTable
GROUP BY Family
SELECT Family
FROM #TempFamily
WHERE CountIt = 1
Smith
Thompson
Gorgon
-----------------------------------------------------------
Query 3:
SELECT ChildAge, COUNT(*) as CountIt
FROM FamilyTable A
INNER JOIN #TempFamily B
ON A.Family = B.Family
WHERE B.CountIt = 1
ChildAge CountIt
4 2
5 1
DROP TABLE #TempFamily
-----------------------------------------------------------
November 15, 2006 at 11:23 pm
It's probably best if you explain what you want the queries to do. From what I can see,
Query 1 returns, for each age, the number of children with that age
Query 2 returns the families with only one child. You can put this into one query by saying
select Family
from FamilyTable
group by Family
having count(*) = 1
The having clause is like a where clause but applies to groups instead.
Query 3 returns, for each age, the number of families with a single child of that age.
This can be combined like so (there are quite a few ways to do this)
select ChildAge, count(*)
from FamilyTable FT
inner join (select Family from FamilyTable group by Family having count(*) = 1) SingleChildFamilies SCF
on FT.Family = SCF.Family
group by ChildAge
This query uses a nested select statement (the statement from query #2) and joins the family table to it.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply