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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Jim HornSQL Server Data DudeCommented:
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.
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'.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Habib PourfardSoftware 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
ptslvAuthor Commented:
Is this correct?

;WITH  Survay AS (......

What is in front of the ";"?
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
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.
Habib PourfardSoftware DeveloperCommented:
you cannot use an alias in group by clause.

but the simple select query works:
SELECT 'Bat Charge' AS ColumnName
LowfatspreadCommented:
"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...?
ptslvAuthor Commented:
The semicolon is the end of my query in my code.
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.