Joining mulitple tables and using Count() function

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

General SQL Server Forums

New to SQL Server Programming

Joining mulitple tables and using Count() function

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-11-11 : 02:41:36

Dear Sir/ Madam

I have a database with the following tables:

1. HR-Department
(ID int PK,
DepartmentName varchar(250)
)

2. ProgrammeDepartment
(ID int PK,
DepartmentID int (FK to HR-Department.ID)
ProgrammeID int (FK to Programme.ID)
)

3. Programme
(ID int PK,
ProgrammeName varchar(250)
)

4. ProgrammePeriod
(ID int PK,
ProgrammeID int (FK to Programme.ID)
ProgrammePeriodName varchar(50)
)

5. MemberProgrammePeriod
(ID int PK,
ProgrammePeriodID int (FK to ProgrammePeriod.ID)
MemberType int (FK to MemberType.ID)
MemberID int (FK to member.ID)
DateOfApplication DateTime
)

6. MemberType
(ID int PK,
MemberTypeName varchar(50))

I would like to select

Department, Programme, Count of how many memberType ID=1, Count of how many memberTypeID= 2, Count of how many memberType 3, Count of how many Member Type 4 (to the last member type), grouped by the department and programme where MemberProgramPeriod.DateOfApplication between @startDate and @endDate

Ideally, I am looking for a script that automatically include new membertypes into the query if possible.

i.e. If I add a new record into membertypes table with ID n, it should automatically return

Department, Program, Count of members in MemberType 1, ... , Count of members in MemberType n

Any help in how to do this is very much appreciate.

God Bless.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-11 : 02:51:05

Expected output format ?

Type1 , Type2
10 12

or

Type
Type1 10
Type2 12

What version of sql you are using ?

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-11-11 : 02:57:50

Expected output format:

Department, Programme, CountMemberTypeID1 , CountMemberTypeID2 CountMemberTypeID3
------------------------------------------------------------------------------------
Marketing, Website , 40 , 0 , 1

Version MSSQL 2008 R2

Sachin.Nand

2937 Posts

Posted - 2010-11-11 : 03:16:58

Have a look here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=152505

PBUH

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-11-11 : 03:25:46

Hello Sachin,

Thanks for your help.

I am a bit lost here. Please can you explain what is going on in your sql code so that I may understand what is to be done? This will allow me do it myself when faced with a similar situation in future. Right now I got your sql code, and I found that I do not understand what your code is doing or how it arrives at the answer. Having some comments in the code block will help a lot in letting me know what is going on so that I may change it to fit my needs.

Thanks. :)

Sachin.Nand

2937 Posts

Posted - 2010-11-11 : 03:29:58

Did you try running my code?

PBUH

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-11-11 : 03:43:31

yes I did. :) But I still don't get it. I am more like a novice and still got tons to learn, so please be patient ok?

Let me see if i guess your code correct

-- Declare a variable
declare @collist as varchar(max)=''

--Assign the variable (but not sure what it is trying to assign.. :(
select @collist=@collist + rid from(
select ',[ScoreRaw' + convert(varchar(10),ROW_NUMBER()over(partition by firstname order by firstname)) + ']' rid from #tbl
)t group by rid

declare @sql as varchar(max)

-- (why did you select @sql = .....
-- why not just have select * from ... instead of having a variable in a string then execute the string

select @sql='select * from

(

-- I am lost here. :(
select FirstName,col,ScoreRaw from
(
select FirstName,''ScoreRaw'' + '''' + convert(varchar(10),ROW_NUMBER()over(partition by firstname order by firstname))col,ScoreRaw from #tbl
)t

)u pivot

(max(scoreraw) for col in(' + stuff(@collist,1,1,'') + '))v'

exec (@sql)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources