Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Adding product of a subquery to a subquery fails
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

student_4187
Starting Member

4 Posts

Posted - 07/06/2014 :  17:56:17  Show Profile  Reply with Quote
Hello,

I am trying to add the results of both of these queries together:

The purpose of the first query is to find the number of nulls in
the TimeZone column.

Query 1:

SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename

The purpose of the second query is to find results in the
AAST, AST, etc timezones.

Query 2:

SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')

Note: both queries produce a whole number with no decimals. Ran individually both queries produce accurate results. However, what I would like is one query which produced a single INT by adding both results together. For example, if Query 1 results to 5 and query 2 results to 10, I would like to see a single result of 15 as the output.

What I came up with (from research) is:

SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST'))

I get a msq 102, level 15, state 1 error.
I also tried

SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')) as IVR_HI_n_AK_results

but I still get an error. For the exact details see:

http://www.photo-host.org/Yr

NOTE: the table in query 1 and query 2 are the same table. I am using T-SQL in SQL Server Management Studio 2008. I'm fairly new to SQL and have been working with this "bug" for a few weeks. Thanks in advance for your help.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 07/06/2014 :  19:08:12  Show Profile  Reply with Quote
Try this:
select sum(c)
  from (SELECT COUNT(*) - COUNT (TimeZone) as c
          FROM tablename
        union all
        SELECT COUNT (TimeZone) as c
          FROM tablename
         WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')
       ) as a
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 07/07/2014 :  02:52:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT	COUNT(*) - COUNT(TimeZone) - SUM(CASE WHEN TimeZone NOT IN ('EST', 'MST', 'PST', 'CST') THEN 1 ELSE 0 END)
FROM	dbo.TableName;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

yado
Starting Member

Iraq
8 Posts

Posted - 07/07/2014 :  03:33:38  Show Profile  Reply with Quote
unspammed
Go to Top of Page

student_4187
Starting Member

4 Posts

Posted - 07/07/2014 :  23:26:13  Show Profile  Reply with Quote
Thanks everyone. I tried the code block that starts

select sum(c)

and modified it slightly. It does exactly what I needed. I'm wondering about

SELECT COUNT(*) - COUNT(TimeZone) - SUM(CASE WHEN TimeZone NOT IN ('EST', 'MST', 'PST', 'CST') THEN 1 ELSE 0 END)
FROM dbo.TableName;

I'm not familiar with case. In my limited understanding, it looks like this code would add one for each instance matching the when claus, then sum those. So, if there were 4 instances that fit the WHEN criteria, it would sum to 4. But if the code takes the result of COUNT(*) - COUNT then - SUM ... that could even be a negative number. I think Im missing something.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 07/08/2014 :  05:19:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT	COUNT(*) - COUNT(TimeZone) + SUM(CASE WHEN TimeZone NOT IN ('EST', 'MST', 'PST', 'CST') THEN 1 ELSE 0 END)
FROM	dbo.TableName;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

student_4187
Starting Member

4 Posts

Posted - 07/12/2014 :  01:40:39  Show Profile  Reply with Quote
Thank you all for your help. I will continue to visit here as it has been very helpful.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000