Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# How to count grades that does not exist.

Posted on 2013-06-16
Medium Priority
352 Views
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.

from mstmassstudents
order by 1,2

The sample data is attached.
0
Question by:searchsanjaysharma
• 8
• 5
• 2
• +3

LVL 6

Expert Comment

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

ID: 39252254
I have mstmassstudent table only where grade field is there,
0

LVL 49

Expert Comment

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
from cte
group by
``````
0

Author Comment

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

FROM  mstmassstudents A  right outer join  grades b
order by 1,2
0

LVL 49

Expert Comment

ID: 39252290
SELECT
A.COURSE
, A.SUBJECTCODE
, COUNT(*)
GROUP BY
A.COURSE
, A.SUBJECTCODE
ORDER BY
A.COURSE
, A.SUBJECTCODE
0

LVL 49

Expert Comment

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

ID: 39252298
giving the same output
0

Author Comment

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 course,subjectcode,
from cte
group by

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

LVL 49

Expert Comment

ID: 39252331
``````;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
),
cteS as (
select distinct course,subjectcode from mstmassstudents
),
cte as  (
from cteS
cross join cteG
)
select
cte.course
, cte.subjectcode
from cte
left join mstmassstudents as m on cte.course = m.course
and cte.subjectcode = m.subjectcode
group by
cte.course
, cte.subjectcode
order by
cte.course
, cte.subjectcode
``````
{+ edit see this at: http://sqlfiddle.com/#!3/e8803/5 }
0

LVL 6

Expert Comment

ID: 39252427
Try

FROM  mstmassstudents A  right outer join  grades b
order by 1,2
0

LVL 49

Expert Comment

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

is the equivalent of:

select ...

0

LVL 49

Expert Comment

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

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

LVL 5

Expert Comment

ID: 39253560
Hi SearchSanjaySharma,
``````select a.course,a.subject,a.grade, count(*) as GradeCount
from mstmassstudents  a
UNION
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
``````
0

LVL 32

Expert Comment

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,
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
)		-- This outputs all combinations of course, subjectcode, and grade in one list.
SELECT
BaseColumns.course,
BaseColumns.subjectCode,
FROM BaseColumns
LEFT JOIN (
SELECT
course,
subjectCode,
FROM mstmassstudents
GROUP BY
course,
subjectcode,
ORDER BY BaseColumns.course,
BaseColumns.subjectCode,
``````
0

LVL 49

Accepted Solution

PortletPaul earned 1500 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)

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
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])
ORDER BY
r.course
, r.subjectcode
;
``````
all of these 4 proposals are available for use and comparison at: http://sqlfiddle.com/#!3/ab8e1/3
0

LVL 49

Expert Comment

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

Author Closing Comment

ID: 39291231
tx
0

## Featured Post

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simpâ€¦
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled taskâ€¦
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.