Solved

SQL Query - Case and Union

Posted on 2014-01-08
8
398 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
8 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

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 65

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSIS GUID Variable 2 37
Update the value of table B with value of table A tables inner join 6 78
SQL Distinct Question 3 15
Need to trim my database size 9 26
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 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