Solved

How to count grades that does not exist.

Posted on 2013-06-16
18
337 Views
Last Modified: 2013-07-01
I have an table in which there is a column grade.
Which can have the values A+, A, B+, B, C+, C, D, E, F, I
These grades can be obtained in any subject.
For example: if in any subject grade A+ is not available it should be counted as 0.
But the following query returns only the count of the grades which are available in the table,
I want, if any of the grades does not exist it should be counted as zero.

The following command counts only the grades.

select course,subjectcode,grade,COUNT(*)
from mstmassstudents
group by course,subjectcode,grade
order by 1,2


The sample data is attached.
Grades.xlsx
0
Comment
Question by:searchsanjaysharma
  • 8
  • 5
  • 2
  • +3
18 Comments
 
LVL 6

Expert Comment

by:Peter Kiprop
ID: 39252249
Hi  searchsanjaysharma,

You need to join Grade table master and return all rows from this table and count from mstmassstudents.
0
 

Author Comment

by:searchsanjaysharma
ID: 39252254
I have mstmassstudent table only where grade field is there,
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39252278
;with cte as (
                      select 'A+' as grade union all
                      select 'A' as grade union all
                      select 'B+' as grade union all
                      select 'B' as grade union all
                      select 'C+' as grade union all
                      select 'C' as grade union all
                      select 'D' as grade union all
                      select 'E' as grade union all
                      select 'F' as grade union all
                      select 'I' as grade
       )
select
  cte.grade
, count(m.grade)
from cte
left join mstmassstudents as m on cte.grade = m.grade
group by
   cte.grade

Open in new window

0
 

Author Comment

by:searchsanjaysharma
ID: 39252281
I have created grade table where all grades are written but thsi query doesnt returning the desired result.

      SELECT A.COURSE,A.SUBJECTCODE,a.Grade,COUNT(*)
      FROM  mstmassstudents A  right outer join  grades b
        ON  A.grade=B.GRADE
       GROUP BY A.COURSE,A.SUBJECTCODE,a.Grade
      order by 1,2
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39252290
SELECT
         A.COURSE
        , A.SUBJECTCODE
        , a.Grade
        , COUNT(*)
FROM  grades B
LEFT JOIN mstmassstudents A ON B.grade = A.GRADE
GROUP BY
         A.COURSE
        , A.SUBJECTCODE
        , a.Grade
ORDER BY
         A.COURSE
        , A.SUBJECTCODE
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39252291
use the grades table as the FROM, this will ensure every grade is included when you left join to the details
0
 

Author Comment

by:searchsanjaysharma
ID: 39252298
giving the same output
0
 

Author Comment

by:searchsanjaysharma
ID: 39252301
with cte as (
                      select 'A+' as grade union all
                      select 'A' as grade union all
                      select 'B+' as grade union all
                      select 'B' as grade union all
                      select 'C+' as grade union all
                      select 'C' as grade union all
                      select 'D' as grade union all
                      select 'E' as grade union all
                      select 'F' as grade union all
                      select 'I' as grade
       )
select course,subjectcode,
  cte.grade
, count(m.grade)
from cte
left join mstmassstudents as m on cte.grade = m.grade
group by
   course,subjectcode,cte.grade


This is still missing the grades whih are not thre.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39252331
try this please:
;with cteG as (
                      select 'A+' as grade union all
                      select 'A' as grade union all
                      select 'B+' as grade union all
                      select 'B' as grade union all
                      select 'C+' as grade union all
                      select 'C' as grade union all
                      select 'D' as grade union all
                      select 'E' as grade union all
                      select 'F' as grade union all
                      select 'I' as grade
         ),
cteS as (
          select distinct course,subjectcode from mstmassstudents
        ),
cte as  (
          select course,subjectcode, grade
          from cteS
          cross join cteG
        )
select
  cte.course
, cte.subjectcode
, cte.grade
, count(m.grade)
from cte
left join mstmassstudents as m on cte.course = m.course
                              and cte.subjectcode = m.subjectcode
                              and cte.grade = m.grade
group by
  cte.course
, cte.subjectcode
, cte.grade
order by
  cte.course
, cte.subjectcode
, cte.grade

Open in new window

{+ edit see this at: http://sqlfiddle.com/#!3/e8803/5 }
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 6

Expert Comment

by:Peter Kiprop
ID: 39252427
Try

 SELECT A.COURSE,A.SUBJECTCODE,a.Grade,COUNT(*)
      FROM  mstmassstudents A  right outer join  grades b
        ON  A.grade=B.GRADE
       GROUP BY A.COURSE,A.SUBJECTCODE,a.Grade
      order by 1,2
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39252441
a right join will not make any difference, it is the use of [course] and [subject] code in the group by which is suppressing the outer join effect on grades.

select ...
from mstmassstudents as M
right join grades as G on M.grade = G.grade

is the equivalent of:

select ...
from grades as G
left join mstmassstudents as M on G.grade = M.grade

which has already failed.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39252479
COURSE SUBJECTCODE  A+   A    B+    B     C+    C    D    E    F    I
B.Com    CMP-126    0    0    25    30    25    2    7    0    3    1
B.Com    CMT-121    2    13   16    17    15    12   7    4    6    1
B.Com    CMT-122    5    6    3     7     13    6    6    1    1    0

Open in new window

whilst this is quite different to the original request, perhaps it would be useful
select
  course
, subjectcode
, sum(case when grade = 'A+' then 1 else 0 end) as [A+]
, sum(case when grade = 'A'  then 1 else 0 end) as A
, sum(case when grade = 'B+' then 1 else 0 end) as [B+]
, sum(case when grade = 'B'  then 1 else 0 end) as B
, sum(case when grade = 'C+' then 1 else 0 end) as [C+]
, sum(case when grade = 'C'  then 1 else 0 end) as C
, sum(case when grade = 'D'  then 1 else 0 end) as D
, sum(case when grade = 'E'  then 1 else 0 end) as E
, sum(case when grade = 'F'  then 1 else 0 end) as F
, sum(case when grade = 'I'  then 1 else 0 end) as I
from mstmassstudents
group by
  course
, subjectcode

Open in new window

both this and the cross join based solution - which does produce the full list of grades for every course/subject - are available to try at:
http://sqlfiddle.com/#!3/e8803/14
0
 
LVL 32

Expert Comment

by:awking00
ID: 39253402
Given your sample data just for subjectcode CMP-126, what are your expected results?
0
 
LVL 5

Expert Comment

by:DOSLover
ID: 39253560
Hi SearchSanjaySharma,
Please try this:
select a.course,a.subject,a.grade, count(*) as GradeCount
  from mstmassstudents  a
  JOIN Grades b ON b.grade = a.grade
 Group by a.course,a.subject,a.grade 
UNION 
select distinct b.course, b.subject, a.grade, 0 as gradecount 
 from Grades a, mstmassstudents  b
where (select count(*) from mstmassstudents c where c.course=b.course and c.subject=b.subject and c.grade=a.grade) = 0
order by 1,2,3

Open in new window

0
 
LVL 32

Expert Comment

by:bhess1
ID: 39254584
The biggest issue is that you are grouping by more than just the grade.  To easily handle this, you need a list of all combinations of Course, SubjectCode, and Grade.  This code will provide the list needed, and join the actual grades with it:
;WITH BaseColumns AS (
	SELECT DISTINCT 
		course,
		subjectcode,
		grade,
		gradeorder  -- need this for correct sorting of grades - if you care - with A+ = 10, A=20, B+=30 etc
				-- number by 10s in case they want to add A- or some other variant in the future.
				-- If this does not exist, A sorts above A+
	FROM (
		SELECT DISTINCT 
			course,
			subjectcode
		FROM mstmassstudents
		) AS ms
	CROSS JOIN grades g
	)		-- This outputs all combinations of course, subjectcode, and grade in one list. 
SELECT 
	BaseColumns.course,
	BaseColumns.subjectCode,
	BaseColumns.grade,
	ISNULL(ActualGrades.GradeCount, 0) AS GradeCount
FROM BaseColumns
LEFT JOIN (
	SELECT 
		course,
		subjectCode,
		grade,
		COUNT(*) AS GradeCount
	FROM mstmassstudents
	GROUP BY
		course,
		subjectcode,
		grade
	) AS ActualGrades
	ON BaseColumns.course = ActualGrades.course
	AND  BaseColumns.subjectcode = ActualGrades.subjectcode
	AND  BaseColumns.grade = ActualGrades.grade
ORDER BY BaseColumns.course,
	BaseColumns.subjectCode,
	BaseColumns.gradeorder

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39254863
There are 3 proposals so far that produce a count of every grade for each course/subject

ID: 39252331 - using CROSS JOIN and CTEs (note you had stated you only had 1 table)
ID: 39253560 - using a UNION
ID: 39254584 - using CROSS JOIN and Grades table (adding a sorting column)

nb: any CTE of grades can be replaced by the grades table (or vice-versa)
    a sorting column may be added to a CTE of grades

There remains another way, which does not require any CTE or the Grades table, and it is has the best execution plan so far. This approach uses a CROSS APPLY:
SELECT
  r.course
, r.subjectcode
, ca1.grade
, ca1.gradecount
FROM (
      SELECT  -- this subquery produces a column of counts for each grade
              -- if new grades are introduced then new sum(...) rows would be needed for each new grade
        course
      , subjectcode
      , sum(CASE WHEN grade = 'A+' THEN 1 ELSE 0 END) AS [A+]
      , sum(CASE WHEN grade = 'A'  THEN 1 ELSE 0 END) AS A
      , sum(CASE WHEN grade = 'B+' THEN 1 ELSE 0 END) AS [B+]
      , sum(CASE WHEN grade = 'B'  THEN 1 ELSE 0 END) AS B
      , sum(CASE WHEN grade = 'C+' THEN 1 ELSE 0 END) AS [C+]
      , sum(CASE WHEN grade = 'C'  THEN 1 ELSE 0 END) AS C
      , sum(CASE WHEN grade = 'D'  THEN 1 ELSE 0 END) AS D
      , sum(CASE WHEN grade = 'E'  THEN 1 ELSE 0 END) AS E
      , sum(CASE WHEN grade = 'F'  THEN 1 ELSE 0 END) AS F
      , sum(CASE WHEN grade = 'I'  THEN 1 ELSE 0 END) AS I
      FROM mstmassstudents
      GROUP BY
        course
      , subjectcode
      ) AS r
CROSS APPLY (   -- the cross apply "unpivots" the data supplied from the above subquery
                -- from one column per Grade, 
                -- to one column of grades and one column of counts
  VALUES
          (1 ,'A+',r.[A+]) -- adding a sorting column is simple here
         ,(2 ,'A' ,r.[A])  -- and if further grades are to be included
         ,(3 ,'B+',r.[B+]) -- they would be added here
         ,(4 ,'B' ,r.[B])  -- making necessary change for sorting
         ,(5 ,'C+',r.[C+])
         ,(6 ,'C' ,r.[C])
         ,(7 ,'D' ,r.[D])
         ,(8 ,'E' ,r.[E])
         ,(9 ,'F' ,r.[F])
         ,(10,'I' ,r.[I])
      ) AS ca1 (gradesort, grade, gradecount)
ORDER BY
  r.course
, r.subjectcode
, ca1.gradesort
;

Open in new window

all of these 4 proposals are available for use and comparison at: http://sqlfiddle.com/#!3/ab8e1/3
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39262458
Hi, have you tried any of these as yet?
0
 

Author Closing Comment

by:searchsanjaysharma
ID: 39291231
tx
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQl works but add Having  by null or value 11 44
Review MS SQL cluster diagram 9 79
SQL HELP 2 82
Move SQL 2005 Express to Server 2012R2 19 100
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

932 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

11 Experts available now in Live!

Get 1:1 Help Now