Solved

Get the Sum of a count of a group

Posted on 2008-10-28
14
185 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
Comment Utility
select Student,  LessonID, count(*) as TotalNumberOfLessonsInSyllabus
from      #CompletedCBTS
group by LessonID, Student
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 62 total points
Comment Utility
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
Comment Utility
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 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 62 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Accepted Solution

by:
TechSinger earned 252 total points
Comment Utility
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
Comment Utility
Lol, copied that without looking.  Talk about doh!
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility

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
Comment Utility
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
Comment Utility
Thanks Guys.
0
 
LVL 3

Expert Comment

by:TechSinger
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

743 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

14 Experts available now in Live!

Get 1:1 Help Now