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 2000 Forums
 SQL Server Development (2000)
 [Resolved] Getting a Count from different table?

Author  Topic 

DTFan
Yak Posting Veteran

52 Posts

Posted - 2005-12-09 : 14:05:20
I *hope* that I explain and do this correctly. OK. I'm trying to figure out how to get a count from a table not needed elsewhere in the query. I have a couple of tables that I'm using in my query (tblStudent, tblMajor, tblStudentType) which is returning information that I requested. I want to add one more item to the Select statement using a fourth table (tblStudentClass). The query now is returning a list of all students, their "Type" (Full Time, Part Time, etc), their name, email address and major. What I would like to add is a Count from the fourth table as to how many time that studentID is listed (which would indicate how many classes they are currently enrolled in). I'm including a script which was generated by the GUI that I'm using for MSDE 2000. I've removed the comments.


CREATE TABLE [tblStudent] (
[studID] int IDENTITY(1, 1) NOT NULL,
[majID] tinyint NOT NULL,
[stypeID] tinyint NOT NULL,
[studFName] varchar(35) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL,
[studLName] varchar(35) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL,
[studEmail] varchar(75) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL
)
ON [PRIMARY]
GO

EXEC sp_addextendedproperty 'MS_Description', N'major ID foreign key', 'user', 'dbo', 'table', 'tblStudent', 'column', 'majID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'student type', 'user', 'dbo', 'table', 'tblStudent', 'column', 'stypeID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'students first name', 'user', 'dbo', 'table', 'tblStudent', 'column', 'studFName'
GO

EXEC sp_addextendedproperty 'MS_Description', N'students last name', 'user', 'dbo', 'table', 'tblStudent', 'column', 'studLName'
GO

EXEC sp_addextendedproperty 'MS_Description', N'students email address', 'user', 'dbo', 'table', 'tblStudent', 'column', 'studEmail'
GO

CREATE TABLE [tblMajor] (
[majID] tinyint IDENTITY(1, 1) NOT NULL,
[depID] tinyint NOT NULL,
[majTitle] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL
)
ON [PRIMARY]
GO

EXEC sp_addextendedproperty 'MS_Description', N'what department is this major a part of?', 'user', 'dbo', 'table', 'tblMajor', 'column', 'depID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'name of the major?', 'user', 'dbo', 'table', 'tblMajor', 'column', 'majTitle'
GO

CREATE TABLE [tblStudentClass] (
[scID] int IDENTITY(1, 1) NOT NULL,
[studID] int NOT NULL,
[claID] int NOT NULL,
[profID] int NOT NULL,
[schedID] int NOT NULL
)
ON [PRIMARY]
GO

EXEC sp_addextendedproperty 'MS_Description', N'students ID (foreign key)', 'user', 'dbo', 'table', 'tblStudentClass', 'column', 'studID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'tblclass foreign key', 'user', 'dbo', 'table', 'tblStudentClass', 'column', 'claID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'f/k to professor table', 'user', 'dbo', 'table', 'tblStudentClass', 'column', 'profID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'what schedule is this class on (f/k to tblSchedule)', 'user', 'dbo', 'table', 'tblStudentClass', 'column', 'schedID'
GO

CREATE TABLE [tblStudentType] (
[stypeID] tinyint IDENTITY(1, 1) NOT NULL,
[stypeTitle] varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL
)
ON [PRIMARY]
GO

SET IDENTITY_INSERT [tblMajor] ON
GO

INSERT INTO [tblMajor] ([majID], [depID], [majTitle])
VALUES
(1, 1, 'Temp Major')
GO

INSERT INTO [tblMajor] ([majID], [depID], [majTitle])
VALUES
(2, 3, 'Physics')
GO

INSERT INTO [tblMajor] ([majID], [depID], [majTitle])
VALUES
(3, 2, 'American Lit')
GO

INSERT INTO [tblMajor] ([majID], [depID], [majTitle])
VALUES
(4, 10, 'European Civilization')
GO

INSERT INTO [tblMajor] ([majID], [depID], [majTitle])
VALUES
(5, 9, 'Political Science')
GO

INSERT INTO [tblMajor] ([majID], [depID], [majTitle])
VALUES
(6, 2, '18th Century Poetry')
GO

INSERT INTO [tblMajor] ([majID], [depID], [majTitle])
VALUES
(7, 4, 'Sports Physiology')
GO

INSERT INTO [tblMajor] ([majID], [depID], [majTitle])
VALUES
(8, 8, 'Drama')
GO

INSERT INTO [tblMajor] ([majID], [depID], [majTitle])
VALUES
(9, 10, 'American History')
GO

SET IDENTITY_INSERT [tblMajor] OFF
GO

COMMIT
GO

SET IDENTITY_INSERT [tblStudent] ON
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES
(1, 1, 1, 'Temp', 'Student', 'no@no.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES
(2, 9, 1, 'Ali', 'Larter', 'ali@finaldestination.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES
(3, 9, 1, 'Amy', 'Smart', 'amy@butterflyeffect.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES
(4, 8, 2, 'Katie', 'Holmes', 'katie@thecreek.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES
(5, 9, 1, 'Jordana', 'Brewster', 'jordana@fastandfurious.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES
(6, 7, 3, 'Anna', 'Kournikova', 'anna@tennis.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES
(7, 2, 2, 'Rhona', 'Mitra', 'rhona@niptuck.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES
(8, 5, 1, 'Julie', 'Bowen', 'jbowen@bostonlegal.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES
(9, 2, 4, 'Lake', 'Bell', 'lakeb@surface.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES
(10, 4, 3, 'Keira', 'Knightly', 'keira@thejacket.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES
(11, 7, 2, 'Jennifer', 'Morrison', 'jen@house.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES
(12, 8, 3, 'Evangeline', 'Lilly', 'elilly@lost.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES
(13, 9, 4, 'Jennifer', 'Finnigan', 'jennifer@closetohome.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES
(14, 6, 2, 'Mila', 'Kunis', 'mila@that70sshow.com')
GO

SET IDENTITY_INSERT [tblStudent] OFF
GO

COMMIT
GO

SET IDENTITY_INSERT [tblStudentClass] ON
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(1, 1, 1, 1, 1)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(2, 2, 22, 11, 4)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(3, 2, 4, 34, 14)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(4, 10, 9, 17, 5)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(5, 5, 4, 34, 14)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(6, 5, 20, 11, 12)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(7, 5, 21, 23, 35)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(8, 2, 21, 23, 35)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(9, 8, 18, 21, 5)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(10, 8, 19, 72, 27)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(11, 8, 9, 17, 5)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(12, 12, 23, 33, 10)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(13, 12, 8, 91, 19)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(14, 9, 2, 64, 9)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(15, 9, 14, 6, 15)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(16, 11, 7, 51, 9)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(17, 2, 20, 11, 12)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(18, 14, 17, 81, 10)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(19, 14, 15, 40, 4)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(20, 3, 20, 11, 12)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(21, 13, 20, 11, 12)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(22, 13, 4, 34, 14)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(23, 4, 19, 72, 27)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(24, 3, 14, 6, 15)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(25, 3, 11, 17, 8)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(26, 3, 22, 11, 4)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES
(27, 3, 18, 21, 5)
GO

SET IDENTITY_INSERT [tblStudentClass] OFF
GO

COMMIT
GO

SET IDENTITY_INSERT [tblStudentType] ON
GO

INSERT INTO [tblStudentType] ([stypeID], [stypeTitle])
VALUES
(1, 'Full Time')
GO

INSERT INTO [tblStudentType] ([stypeID], [stypeTitle])
VALUES
(2, 'Part Time')
GO

INSERT INTO [tblStudentType] ([stypeID], [stypeTitle])
VALUES
(3, 'Graduate')
GO

INSERT INTO [tblStudentType] ([stypeID], [stypeTitle])
VALUES
(4, 'Evening')
GO

SET IDENTITY_INSERT [tblStudentType] OFF
GO

COMMIT
GO

ALTER TABLE [dbo].[tblStudent]
ADD CONSTRAINT [PK_tblStudent] PRIMARY KEY CLUSTERED ([studID])
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblMajor]
ADD CONSTRAINT [PK_tblMajor] PRIMARY KEY CLUSTERED ([majID])
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblStudentClass]
ADD CONSTRAINT [PK_tblStudentClass] PRIMARY KEY CLUSTERED ([scID])
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblStudentType]
ADD PRIMARY KEY CLUSTERED ([stypeID])
ON [PRIMARY]
GO


The query that I have so far is this:


SELECT
stype.stypeTitle,
stud.studFName + ' ' + stud.studLName AS studName,
stud.studEmail,
maj.majTitle
FROM
tblStudent AS stud
INNER JOIN tblMajor AS maj ON (stud.majID = maj.majID)
INNER JOIN tblStudentType AS stype ON (stud.stypeID = stype.stypeID)
WHERE stud.studID > 1
ORDER BY stype.stypeID ASC, stud.studLName ASC, stud.studFName ASC


That works, but it's not returning the Count (which I want to be the last column). So the output should look like this (without, of course, the pipes):

quote:

Full Time | Julie Bowen | emailaddresshere | Political Science | 3
Full Time | Jordana Brewster | emailaddresshere | American History | 3
Full Time | Ali Larter | emailaddresshere | American History | 4
Full Time | Amy Smart | emailaddresshere | American History | 5
Part Time | Katie Holmes | emailaddresshere | Drama | 1
Part Time | Mila Kunis | emailaddresshere | 18th Century Poetry | 2
Part Time | Rhona Mitra | emailaddresshere | Physics | 0
Part Time | Jennifer Morrison | emailaddresshere | Sports Physiology | 1
Graduate | Keira Knightly | emailaddresshere | European Civilization | 1
Graduate | Anna Kournikova | emailaddresshere | Sports Physiology | 0
Graduate | Evangeline Lilly | emailaddresshere | Drama | 2
Evening | Lake Bell | emailaddresshere | Physics | 2
Evening | Jennifer Finnigan | emailaddresshere | American History | 2


Anyway, I've tried including a Count(tblStudentClass.studID), but all it returns are 1's for each person (although each listing in tblSTudentClass was returned so you could see Amy Smart listed 5 times, Ali Larter listed 4 times, etc.). As you can see from the query, I'm getting the students grouped by their type and then sorting by last name, first name. As I said, this query is working exactly as I'd like except that I can't get a count to show up.

Any idea's?

Thank you in advance for any and all help/assistance/tutorials/pointers etc that you can give. I'm pretty sure the script should work as it did work on my computer (once I had the database created (with no tables)). I'm using EMS MSSQL Manager as the GUI in case that matters. If there is anything else I need to provide, please let me know.

And thanks again for any help that you can give.

DTFan
Ever-hopeful programmer-in-training

nu_dba
Starting Member

8 Posts

Posted - 2005-12-09 : 15:17:58
When you tried:

Anyway, I've tried including a Count(tblStudentClass.studID), but all it returns are 1's for each person

did you also INNER JOIN the tblStudentClass table with the tblStudent ON (stud.studID = tblStudentClass.studID)? I don't have access to a DB right now to play around with your tables so I'm trying to visual how the join with the student class table would affect the result set.

-- nu_dba
Go to Top of Page

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-09 : 16:14:15
What count u need ?
Don't u use Group By ?

PS : Its very hard to go thru ur whole Q as it contains all the Insert Statements. It would be much easier if u give some sample data.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-12-10 : 00:33:57
[code]
SELECT
stype.stypeTitle,
stud.studFName + ' ' + stud.studLName AS studName,
stud.studEmail,
maj.majTitle,
COUNT(SC.studID) AS [MyCount] -- Do not use COUNT(*) as that will count NULL results in tblStudentClass
FROM
tblStudent AS stud
INNER JOIN tblMajor AS maj ON (stud.majID = maj.majID)
INNER JOIN tblStudentType AS stype ON (stud.stypeID = stype.stypeID)
LEFT OUTER JOIN tblStudentClass AS SC ON SC.studID = stud.studID
WHERE stud.studID > 1
GROUP BY
stype.stypeTitle,
stud.studFName + ' ' + stud.studLName,
stud.studEmail,
maj.majTitle,
stype.stypeID,
stud.studLName,
stud.studFName

ORDER BY stype.stypeID ASC, stud.studLName ASC, stud.studFName ASC
[/code]
Should do the trick, note that I have had to add the columns in your ORDER BY to the GROUP BY, otherwise they won't be in "scope".

"Its very hard to go thru ur whole Q as it contains all the Insert Statements. It would be much easier if u give some sample data"

The insert statements are the sample data. DTFan even provided expected results, AND included the edge condition for a student NOT having a class.

Kristen
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-12-10 : 12:59:23
Let us know if you need more help DTFan. This was a perfect post with sample data and expected results. We appreciate when people take the time to do this.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-12-10 : 13:40:53
Blimey! I don't often see you type that "MeanOldDBA" - praise indeed.

Kristen
Go to Top of Page

DTFan
Yak Posting Veteran

52 Posts

Posted - 2005-12-12 : 08:16:52
First off, sorry about no reply over the weekend. No internet connection at home ... I think I actually started going through withdrawals.

Kristen - Thank you. That worked perfectly. And you were right, I was using Count(*) so even if I had gotten it right (which I hadn't because what you provided was quite a bit above my level), it wouldn't have been right.

derrickleggett and Kristen - thanks for the comments about the post. I was worried that I had done it wrong again.

Once again, this site rocks. I'm editingthe original post to indicate [Resolved]! You gurus have made my day!

DTFan
Ever-hopeful programmer-in-training
Go to Top of Page
   

- Advertisement -