how to use group - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to use group

Author

Topic

subhaoviya
Posting Yak Master

135 Posts

Posted - 2010-06-23 : 13:18:09

i have 4 tables like

Table 1
PlanID
planname

Table 2
GPID
PlanID

Table 3
GPID
MEMID

Table 4
MEMID

I need to get the count of memid for each planid.

how to do that
please help me

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-23 : 13:29:15

you need to join the tables first

SELECT a.PlanID, c.MEMID, COUNT(*)
FROM TABLE1 a
INNER JOIN Table2 b
ON a.PlanID = b.PlanId
INNER JOIN Table3 c
ON b.GPID = c.GPID
GROUP BY a.PlanID, c.MEMID

I don't think you need table4

If you want some more detailed help, you have to supply us with.....

come say it!

DETAILS

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

subhaoviya
Posting Yak Master

135 Posts

Posted - 2010-06-23 : 13:46:35

thanks a lot for you reply.

but it is not working properly. it display plan count for each memid

thanks
by subha

Sachin.Nand

2937 Posts

Posted - 2010-06-23 : 14:17:23

quote:
Originally posted by subhaoviya

thanks a lot for you reply.

but it is not working properly. it display plan count for each memid

thanks
by subha

This?



SELECT a.PlanID,COUNT(c.MEMID)
FROM TABLE1 a
INNER JOIN Table2 b
ON a.PlanID = b.PlanId
INNER JOIN Table3 c
ON b.GPID = c.GPID
GROUP BY a.PlanID

If the above also is not working as expected you need to post some sample data.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources