?
Solved

SQL Query - Case and Union

Posted on 2014-01-08
8
Medium Priority
?
406 Views
Last Modified: 2014-01-09
I am building a query from SQL database tables for a report on my webpage - I have my datagrid populating but the counts are incorrect.  Please check to see if I have the query correct.  This is my query:

l_strsql = ";WITH  MyCounts  AS (
                 SELECT  
                   CASE  WHEN ((a is NULL or a = '') AND (b is NULL or b = '') AND (c is NULL or c = '' ))
                    THEN 1  ELSE 0 END AS NV,
                    CASE WHEN (a = 'Bad' AND b = 'Bad' AND c = 'Bad' ) THEN 1 ELSE 0  END AS Neg,
                    CASE WHEN (a = 'Good' AND b = 'Good' AND c = 'Good' )THEN 1 ELSE 0  END as Pos,
                    CASE WHEN (a = 'Neutral' AND b = 'Neutral' AND c = 'Neutral' ) THEN 1 ELSE 0  END as Neut, 'Table 1' AS TableName, Status
                  FROM tbl_1            WHERE (Status='Done')
                 UNION SELECT  
                   CASE  WHEN ((a is NULL or a = '') AND (b is NULL or b = '') AND (c is NULL or c = '' ))
                    THEN 1  ELSE 0 END AS NV,
                    CASE WHEN (a = 'Bad' AND b = 'Bad' AND c = 'Bad' ) THEN 1 ELSE 0  END AS Neg,
                   CASE WHEN (a = 'Good' AND b = 'Good' AND c = 'Good' )THEN 1 ELSE 0  END as Pos,
                   CASE WHEN (a = 'Neutral' AND b = 'Neutral' AND c = 'Neutral' ) THEN 1 ELSE 0  END as Neut, 'Table 2' AS TableName, Status
                  FROM tbl_2    WHERE (Status='Done')
                  UNION SELECT  
                   CASE  WHEN ((a is NULL or a = '') AND (b is NULL or b = '') AND (c is NULL or c = '' ))  THEN 1  ELSE 0 END AS NV,
                   CASE WHEN (a = 'Bad' AND b = 'Bad' AND c = 'Bad' ) THEN 1 ELSE 0  END AS Neg,
                   CASE WHEN (a = 'Good' AND b = 'Good' AND c = 'Good' )THEN 1 ELSE 0  END as Pos,
                    CASE WHEN (a = 'Neutral' AND b = 'Neutral' AND c = 'Neutral' ) THEN 1 ELSE 0  END as Neut, 'Table 3' AS TableName, Status             FROM tbl_3
                  WHERE (Status='Done')
                  )
                  SELECT SUM(NV) AS NotViewed, SUM(Neg) AS Negative, SUM(Good) AS Positive, SUM(Neut) AS Neutral, TableName FROM MyCounts GROUP BY TableName, Status;";
                 

Correct output should be:

TableName       NotViewed     Negative     Positive    Neutral
Table 1                  3                   2               4              1
Table 2                  0                   1               3              3
Table 3                  1                   1               2              1


My output is showing:

TableName       NotViewed     Negative     Positive    Neutral
Table 1                  1                   0               0             0
Table 2                  1                   0               0              0
Table 3                  1                   0               0             0
0
Comment
Question by:ptslv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 39766395
<wild knee-jerk reactions>

UNION eliminates duplicates, UNION ALL does not.

>CASE  WHEN ((a is NULL or a = '') AND (b is NULL or b = '') AND (c is NULL or c = '' ))
CASE WHEN ISNULL(a, '') = '' AND ISNULL(b, '') = '' AND ISNULL(c, '')  = ''
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39766399
can you also provide the sample data of table1,2,3
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 39766401
Without sample data for tbl_1, tbl_2, tbl_3, it is hard to determine if there is an error in your SQL.

If you run this in SSMS you are getting the same invalid output?
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 13

Expert Comment

by:Jeff Darling
ID: 39766412
I think some of your field names may be inconsistant.

NV,NEG,POS, NEUT or NV,NEG,GOOD,NEUT

Can you repost using a code block?
Can you post some sample code to create the tables and insert some sample data?



SELECT CASE 
		WHEN (
				(
					a IS NULL
					OR a = ''
					)
				AND (
					b IS NULL
					OR b = ''
					)
				AND (
					c IS NULL
					OR c = ''
					)
				)
			THEN 1
		ELSE 0
		END AS NV
	,CASE 
		WHEN (
				a = 'Bad'
				AND b = 'Bad'
				AND c = 'Bad'
				)
			THEN 1
		ELSE 0
		END AS Neg
	,CASE 
		WHEN (
				a = 'Good'
				AND b = 'Good'
				AND c = 'Good'
				)
			THEN 1
		ELSE 0
		END AS Pos
	,CASE 
		WHEN (
				a = 'Neutral'
				AND b = 'Neutral'
				AND c = 'Neutral'
				)
			THEN 1
		ELSE 0
		END AS Neut
	,'Table 1' AS TableName
	,STATUS
FROM tbl_1
WHERE (STATUS = 'Done')

UNION

SELECT CASE 
		WHEN (
				(
					a IS NULL
					OR a = ''
					)
				AND (
					b IS NULL
					OR b = ''
					)
				AND (
					c IS NULL
					OR c = ''
					)
				)
			THEN 1
		ELSE 0
		END AS NV
	,CASE 
		WHEN (
				a = 'Bad'
				AND b = 'Bad'
				AND c = 'Bad'
				)
			THEN 1
		ELSE 0
		END AS Neg
	,CASE 
		WHEN (
				a = 'Good'
				AND b = 'Good'
				AND c = 'Good'
				)
			THEN 1
		ELSE 0
		END AS Pos
	,CASE 
		WHEN (
				a = 'Neutral'
				AND b = 'Neutral'
				AND c = 'Neutral'
				)
			THEN 1
		ELSE 0
		END AS Neut
	,'Table 2' AS TableName
	,STATUS
FROM tbl_2
WHERE (STATUS = 'Done')

UNION

SELECT CASE 
		WHEN (
				(
					a IS NULL
					OR a = ''
					)
				AND (
					b IS NULL
					OR b = ''
					)
				AND (
					c IS NULL
					OR c = ''
					)
				)
			THEN 1
		ELSE 0
		END AS NV
	,CASE 
		WHEN (
				a = 'Bad'
				AND b = 'Bad'
				AND c = 'Bad'
				)
			THEN 1
		ELSE 0
		END AS Neg
	,CASE 
		WHEN (
				a = 'Good'
				AND b = 'Good'
				AND c = 'Good'
				)
			THEN 1
		ELSE 0
		END AS Pos
	,CASE 
		WHEN (
				a = 'Neutral'
				AND b = 'Neutral'
				AND c = 'Neutral'
				)
			THEN 1
		ELSE 0
		END AS Neut
	,'Table 3' AS TableName
	,STATUS
FROM tbl_3
WHERE (STATUS = 'Done')

SELECT SUM(NV) AS NotViewed
	,SUM(Neg) AS Negative
	,SUM(Good) AS Positive
	,SUM(Neut) AS Neutral
	,TableName
FROM MyCounts
GROUP BY TableName
	,STATUS

Open in new window

0
 

Author Comment

by:ptslv
ID: 39766461
Jim, I changed UNION to UNION ALL, but did not make any other changes; now I am getting correct counts in the NotViewed column, but am still getting errors in the others.  

Shaun, when I run the query in SSMS, I do not get any errors in syntax.  

Jeffld, I will relook at my fields to see if they are consistent.

In my table records, I have groups (a, b, and c) who are rating the overall information of the record.  If they all rate the same accross the board, then I want a 1 added to the count for the table.  If the overall rating varies, then the record does not count.  That is the way my customer wants to view this particular report.
0
 

Author Closing Comment

by:ptslv
ID: 39766621
Thank you, everyone, for your input.  I made all the changes that Jim suggested, and my query worked.  My numbers were wrong because the data itself was not consistent - I was looking at an incorrect field.  Changing up the CASE for NULL cleaned my query up a bit.  Thank you again for all your help!
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39766714
btw I wrote an article on SQL Server CASE solutions if that helps.

Thanks for the grade.  Good luck with your project.  -Jim
0
 

Author Comment

by:ptslv
ID: 39769139
Thank you, Jim.  I read your article.  Very informative.  Unfortunately, our company has not been authorized to use SQL 2012 yet.  I will keep your article in my SQL tool box for reference.  Have a good day!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

764 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