SQL Query using multiple CASE

In  my sql table, there are multiple teams that can rate a record.  I am tasked to build a report to display on a web page that counts ratings that are the same for a record. I am trying to build my query using CASE.  If I use only one WHEN statement, my datagrid populates.  But if I use multiple WHEN I am getting an error:  "Error near WHEN".
   
Table data looks like this:

Record         MyRating       YourRating        HisRating
1                   Good              Best                 Poor
2                   Good              Good                Good
3                    Best               Best                 Best
4                   Good                                      Good
5  

My query works in access db but I am trying to make it work for the sql table.  Here is my query.  Eventually, it will be a union query for multiple tables.

"SELECT RecNum, CASE " +
             " WHEN (MyRating is NULL or MyRating = '') AND (YourRating is NULL or YourRating = '') AND (HisRating is NULL or HisRating = '' )" +
             " THEN 1  ELSE 0 END AS NotViewed " +
             " WHEN (MyRating = 'Good' AND YourRating = 'Good' AND HisRating = 'Good' )" +
             " THEN 1 ELSE 0  END AS Good "+
             " WHEN (MyRating = 'Best' AND YourRating = 'Best' AND HisRating = 'Best' )" +
             " THEN 1 ELSE 0  END AS Best " +
       " FROM tbl_MySurvey " +
                 " WHERE (Status = 'Done') " +
                 " GROUP BY  RecNum, CASE " +
                " WHEN (MyRating is NULL or MyRating = '') AND (YourRating is NULL or YourRating = '') AND (HisRating is NULL or HisRating = '' )" +
             " THEN 1  ELSE 0 END AS NotViewed " +
             " WHEN (MyRating = 'Good' AND YourRating = 'Good' AND HisRating = 'Good' )" +
             " THEN 1 ELSE 0  END AS Good "+
             " WHEN (MyRating = 'Best' AND YourRating = 'Best' AND HisRating = 'Best' )" +
             " THEN 1 ELSE 0  END AS Best ;";
ptslvAsked:
Who is Participating?
 
Habib PourfardConnect With a Mentor Software DeveloperCommented:
Are you sure? mine is working! to make it short you can write it as follows too:

;WITH  Survay AS (
SELECT RecNum, 
              CASE WHEN (MyRating is NULL or MyRating = '') AND (YourRating is NULL or YourRating = '') AND (HisRating is NULL or HisRating = '' )
              THEN 1  ELSE 0 END AS NotViewed,
              CASE WHEN (MyRating = 'Good' AND YourRating = 'Good' AND HisRating = 'Good' )
              THEN 1 ELSE 0  END AS Good,
              CASE WHEN (MyRating = 'Best' AND YourRating = 'Best' AND HisRating = 'Best' )
              THEN 1 ELSE 0  END AS Best 
        FROM tbl_MySurvey 
		WHERE (Status = 'Done')
) 
SELECT * FROM survay              
GROUP BY  RecNum, NotViewed, Good, Best

Open in new window


by the way I think may be you need to sum up the result as you are grouping by ratings:
;WITH  Survay AS (
SELECT RecNum, 
              CASE WHEN (MyRating is NULL or MyRating = '') AND (YourRating is NULL or YourRating = '') AND (HisRating is NULL or HisRating = '' )
              THEN 1  ELSE 0 END AS NotViewed,
              CASE WHEN (MyRating = 'Good' AND YourRating = 'Good' AND HisRating = 'Good' )
              THEN 1 ELSE 0  END AS Good,
              CASE WHEN (MyRating = 'Best' AND YourRating = 'Best' AND HisRating = 'Best' )
              THEN 1 ELSE 0  END AS Best 
        FROM tbl_MySurvey 
		WHERE ([Status] = 'Done')
) 
SELECT RecNum, SUM(NotViewed), SUM(Good), SUM(Best) FROM survay              
GROUP BY  RecNum

Open in new window

0
 
Habib PourfardSoftware DeveloperCommented:
Here is the Sql equivalent:

SELECT RecNum, CASE 
              WHEN (MyRating is NULL or MyRating = '') AND (YourRating is NULL or YourRating = '') AND (HisRating is NULL or HisRating = '' )
              THEN 1  ELSE 0 END AS NotViewed ,
              CASE WHEN (MyRating = 'Good' AND YourRating = 'Good' AND HisRating = 'Good' )
              THEN 1 ELSE 0  END AS Good ,
              CASE WHEN (MyRating = 'Best' AND YourRating = 'Best' AND HisRating = 'Best' )
              THEN 1 ELSE 0  END AS Best 
        FROM tbl_MySurvey 
                 WHERE (Status = 'Done')
                 GROUP BY  RecNum, CASE 
              WHEN (MyRating is NULL or MyRating = '') AND (YourRating is NULL or YourRating = '') AND (HisRating is NULL or HisRating = '' )
              THEN 1  ELSE 0 END,
              CASE WHEN (MyRating = 'Good' AND YourRating = 'Good' AND HisRating = 'Good' )
              THEN 1 ELSE 0  END,
              CASE WHEN (MyRating = 'Best' AND YourRating = 'Best' AND HisRating = 'Best' )
              THEN 1 ELSE 0  END 

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I have a demo of nested CASE blocks in this article, scroll down about a third of the way.

Looks like you are missing commas after every 'END as column_name'.

btw is it necessary to use dynamic SQL?  Makes it a lot more work handling all of the + 's and quotes.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
ptslvAuthor Commented:
Pourfard,  Thans for the quick response.  I adjusted my query to what you have, but now I am getting  this error:

Incorrect syntax near the keyword 'CASE'.
0
 
ptslvAuthor Commented:
I put in the missing commas and it worked.  Eventually, I will sum it all up.  I have to include some other tables, and I will be working with filters since my groups are actually dynamically generated from whatever is populated in the lookup table.
0
 
ptslvAuthor Commented:
Is this correct?

;WITH  Survay AS (......

What is in front of the ";"?
0
 
Habib PourfardSoftware DeveloperCommented:
If you mean why With is comming with a semicolon:
It is optional, usually included to avoid ambiguity because WITH can be used elsewhere
..FROM..WITH (NOLOCK)..

To read more about With cluase you can refer to MSDN
0
 
ptslvAuthor Commented:
OK, I understand.  One last question - how do I add the tablename as an alias?  I am getting an error.  

"SELECT 'Bat Charge' AS TableName, " +  my case statements .....+
" GROUP BY TableName;";

The error says " A field or property with the name 'TableName' was not found on the selected data source."

It is a bound column in my datagrid.
0
 
Habib PourfardSoftware DeveloperCommented:
you cannot use an alias in group by clause.

but the simple select query works:
SELECT 'Bat Charge' AS ColumnName
0
 
LowfatspreadConnect With a Mentor Commented:
"select tablename,.... from (SELECT 'Bat Charge' AS TableName, " +  my case statements .....+
" ) as x GROUP BY TableName;";

although i'm not clear why you have the semi-colon after tablename in the text...?
0
 
ptslvAuthor Commented:
The semicolon is the end of my query in my code.
0
 
ptslvAuthor Commented:
Thank you for all the help.  This is the final query with the alias column:

strSQL = "";WITH  Survay AS (
  SELECT RecNum,
              CASE WHEN (MyRating is NULL or MyRating = '') AND (YourRating is NULL or YourRating = '') AND (HisRating is NULL or HisRating = '' )
              THEN 1  ELSE 0 END AS NotViewed,
              CASE WHEN (MyRating = 'Good' AND YourRating = 'Good' AND HisRating = 'Good' )
              THEN 1 ELSE 0  END AS Good,
              CASE WHEN (MyRating = 'Best' AND YourRating = 'Best' AND HisRating = 'Best' )
              THEN 1 ELSE 0  END AS Best,  'Bat Charge' AS TableName
        FROM tbl_MySurvey
            WHERE ([Status] = 'Done')
)
SELECT SUM(NotViewed), SUM(Good), SUM(Best), TableName FROM survay              
GROUP BY  TableName
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.