Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Get the Sum of a count of a group

Posted on 2008-10-28
Medium Priority
238 Views
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
``````
0
Question by:rhat
• 5
• 3
• 2
• +3

LVL 60

Assisted Solution

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

LVL 39

Assisted Solution

BrandonGalderisi earned 248 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

jamesgu earned 248 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

LVL 61

Assisted Solution

Kevin Cross earned 248 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
``````
0

LVL 61

Expert Comment

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

LVL 61

Expert Comment

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

ID: 22825179
0

LVL 3

Accepted Solution

TechSinger earned 1008 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
``````
0

LVL 61

Expert Comment

ID: 22826391
Lol, copied that without looking.  Talk about doh!
0

LVL 61

Expert Comment

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
``````
0

Author Comment

ID: 22826444
Feast or famine looks like.  :)

This is what I came up with in the mean time

SELECT
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

ID: 31510891
Thanks Guys.
0

LVL 3

Expert Comment

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

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

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this â€¦
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, â€¦
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
###### Suggested Courses
Course of the Month11 days, 15 hours left to enroll