COUNT Inside Table - 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 2008 Forums

Transact-SQL (2008)

COUNT Inside Table

Author

Topic

sdseal
Starting Member

9 Posts

Posted - 2012-01-12 : 16:04:39

Simplified version of my problem...
Student table (we'll call it StTable) has three fields: StudentID, Date, AbsentCode

There are 4 different absent codes (AE, AU, P, T) that the AbsentCode field may contain.

I need the following output:
StudentID CodeCount

The output will need to show a StudentID and the number of a specific absent code (such as "AE") for a given date within a start date through end date range. There should only be one row per student.

It's the count that's killing me.

Each student should have only one row in the returned table of course.

Simplier statement of what I need: How many codes "AE" did each student have?

Any help would be greatly appreciated.

sdseal
Starting Member

9 Posts

Posted - 2012-01-12 : 16:08:27

That should be ANY date within a date range (that's easy enough). I was trying to help you understand what I was trying to do.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-12 : 16:58:48

Does this work for you?

SELECT
    StudentID,
    COUNT(*) AS CodeCount
FROM
    StTable
WHERE
     AbsentCode =  'AE'
     AND Date BETWEEN <StartDate> AND <EndDate>
GROUP BY        
    StudentID

If not, please post DDL, DML and expected output per this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

sdseal
Starting Member

9 Posts

Posted - 2012-01-12 : 18:25:07

Thanks so much!!
I was missing the GROUP BY.

I appreciate the link to formating questions. I'll be sure to follow the guidelines.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-12 : 18:40:13

sniff...sniff....so proud

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources