Anyone know ms-sql?

[Nikos]

Anyone know ms-sql real well?

Just curious?

I am trying to do some query basics and am having trouble :depressed

[Morphgizmo]

Well, it's been about 3-4 years since I've written SQL, but I still have a decent text book lying around.

[Shelly]

Morph, is that your little girl? She's adorable!

sorry for the hijack.

[Nikos]

:depressed

[Morphgizmo]

Nikos, what are you having trouble with?

Shelly, yes. Thanks for noticing.

[Nikos]

The setup of my code is suppose to go something like this

update groups g1
set NumMems = (select count(...)
from groups as g2, members as m
where ...)

groups is gid, gname, and Num Mems

members table has member information and groupid # which is what I am trying to count. There are like 10 groups and each has a certain amount of people in each. I just want to be able to join the two tables, and insert the counts for each group into NumMems

Im basically trying to make an update, linking the two tables, and inserting the group count for each group into nummems

[Morphgizmo]

Ok, this is what I came up with. I don't think it's exactly what you were looking for, but it may point you in the right direction.

(Edit: I renamed your "Group" Table to "Cluster")

Table #1: Cluster



Cluster_ID Cluster_Name
---------------------------
1 Cluster Schmoe
2 Cluster Smith
3 Cluster Johnson


Table #2: Member



Member_ID Cluster_ID Member_Name
-------------------------------------------
1 1 Joe Shmoe
2 1 Jane Shmoe
3 2 Joe Smith
4 2 Jane Smith
5 3 Joe Johnson
6 3 Jane Johnson
7 2 Chris Smith


With the following query:



SELECT Cluster.Cluster_Name, Member.Cluster_ID,
COUNT(Member.Member_ID) AS NumMems
FROM Member INNER JOIN Cluster ON Cluster.Cluster_ID = Member.Cluster_ID
GROUP BY Member.Cluster_ID, Cluster.Cluster_Name


Gives an output table of:



Cluster_Name Cluster_ID NumMems
---------------------------------------
Cluster Schmoe 1 2
Cluster Smith 2 3
Cluster Johnson 3 2


:nerd