Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query - Case and Union

Posted on 2014-01-08
8
Medium Priority
?
409 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 27

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

609 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