Solved

SQL Query - Case and Union

Posted on 2014-01-08
8
395 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Virtual SQL Server 2014 Standard 35 91
T-SQL 10 35
SQL Syntax Grouping Sum question 7 24
Getting invalid Syntax SQL. 3 19
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…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

860 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