Solved

Get the Sum of a count of a group

Posted on 2008-10-28
14
208 Views
Last Modified: 2010-04-21
Given the following Table:
Student  LessonID    TotalNumLessonsInSyllabus
--------------------------------------------------
Student1    C001               8
Student1    C002               8
Student1    X063               8
Student1    X054                     8
Student2    C004               8
Student2    X054               8
Student2    X056               8
Student3    X063                8
Student3    C004               8
Student3    X054               8
Student3    X056               8


How do I get a total number of LessonID per student.
In this example Student1 = 4; Student2 = 04 and Student3 = 3

Bonus for giving me the count of LessonIDs starting with X and count of Lessons starting with C


select Student,  LessonID, @SumOfTotalLessons as TotalNumberOfLessonsInSyllabus
from	  #CompletedCBTS 
group by LessonID, Student
order by student , LessonID

Open in new window

0
Comment
Question by:rhat
  • 5
  • 3
  • 2
  • +3
14 Comments
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 62 total points
ID: 22824958
select Student,  LessonID, count(*) as TotalNumberOfLessonsInSyllabus
from      #CompletedCBTS
group by LessonID, Student
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 62 total points
ID: 22825065
Try this


select sutdent
,sum(TotalNumLessonsInSyllabus)
,sum(case when left(lessonid)='C' then 1 else 0 end) as C_Count
,sum(case when left(lessonid)='X' then 1 else 0 end) as X_Count
,count(*) as Lesson_Count
from #completedCBTs
group by studentID

0
 
LVL 9

Assisted Solution

by:jamesgu
jamesgu earned 62 total points
ID: 22825070
select Student,  LessonID, count(*) as TotalNumberOfLessonsInSyllabus
, count( case when Left(LessonID, 1)='X' then 1 else 0 end ) as number_of_X_lessons
, count( case when Left(LessonID, 1)='C' then 1 else 0 end ) as number_of_C_lessons
from      #CompletedCBTS
group by Student , LessonID
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 62 total points
ID: 22825071
Chapman's answer is correct, so I will go for the bonus.
select Student,  LessonID
, SUM(CASE LEFT(LessionID,1) WHEN 'X' THEN 1 ELSE 0 END) as TotalNumberOfLessonsInSyllabusX
, SUM(CASE LEFT(LessionID,1) WHEN 'C' THEN 1 ELSE 0 END) as TotalNumberOfLessonsInSyllabusC
from      #CompletedCBTS 
group by LessonID, Student

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22825078
Oh, two slow although, I think you want SUM jamesqu and Brandon's needs the LEFT(..., 1) but is correct.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22825113
Think COUNT works jamesqu when the ELSE is NULL as the aggregate will optimize it out of the count.  Just out of habit always use the SUM as usually I put 1 or 0 and count will count the 0 as an occurrence where as sum will add up values and thus only grabbing the 1's.

This may work:
COUNT(case when Left(LessonID, 1)='C' then 1 end)
OR
COUNT(case when Left(LessonID, 1)='C' then 1 else NULL end)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22825179
0
 
LVL 3

Accepted Solution

by:
TechSinger earned 252 total points
ID: 22825275
All the exmples I am seing are actually showing LessonID, but I got the impression that you wanted a count of LessonID for each student.  I could probably work on the bonus for you too by using the UNION command.
Here is the code I would use.

SELECT T1.Student,'C' AS [Lesson Type],
COUNT(T1.LessonID) AS [Lesson Count]
FROM #CompletedCBTS T1
WHERE LEFT(T1.LessonID,1)='C' 
UNION
SELECT T2.Student,'X' AS [Lesson Type],
COUNT(T2.LessonID) AS [Lesson Count]
FROM #CompletedCBTS T2
WHERE LEFT(T2.LessonID,1)='X' 
ORDER BY [Lesson Count] DESC

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22826391
Lol, copied that without looking.  Talk about doh!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22826401

select Student
, SUM(CASE LEFT(LessionID,1) WHEN 'X' THEN 1 ELSE 0 END) as TotalNumberOfLessonsInSyllabusX
, SUM(CASE LEFT(LessionID,1) WHEN 'C' THEN 1 ELSE 0 END) as TotalNumberOfLessonsInSyllabusC
from      #CompletedCBTS 
group by Student

Open in new window

0
 

Author Comment

by:rhat
ID: 22826444
Feast or famine looks like.  :)

This is what I came up with in the mean time

 SELECT
    Student, Squadron,
    COUNT(*) AS TotalCBTsCompletedPerStudent, (select case when count(Right(LessonID,3)) > =  @CountOfRegCBTs then 'Complete' else 'Incomplete' end) as Complete, @CountOfRegCBTs as RegCBTsInSyllabus,  @CountOfTestOuts as TestOutsInSyllabus
FROM #CompletedCBTs

I haven't worked TechSinger's solution into it yet (but I will).  TS your code required adding a group by in both sections.

TechSinger I think is the closest and I will award him 250 points and split the remaining among the other 4.
Any fast rules of thumb for aggregates and group by?

Thanks Guys.

mwvisa1: got the bonus I think.
0
 

Author Closing Comment

by:rhat
ID: 31510891
Thanks Guys.
0
 
LVL 3

Expert Comment

by:TechSinger
ID: 22827284
Your right rhat.  I left out the Group By's.  Since I don't have any real tables like what you have, I was only able to check the Syntax and I didn't get any error.  I'm sure I would have gotten some errors if I would have tried to execute it.
Thanks for the points.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22827335
Also, there is no reason for this:

case when count(Right(LessonID,3)) > =  @CountOfRegCBTs then 'Complete' else 'Incomplete' end

Now if you wanted to do a distinct right, that's fine.  But counting a right() is an unnecessary string manipulation.

case when count(distinct Right(LessonID,3)) > =  @CountOfRegCBTs then 'Complete' else 'Incomplete' end
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql help 8 55
SSIS with VPN COnnection 2 70
T-SQL: Do I need CLUSTERED here? 13 37
Why i am getting a star, SSMS does not show me any error. Division Error 5 20
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now