SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

student_4187
Starting Member

3 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
Constraint Violating Yak Guru

415 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
30276 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

3 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
30276 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

3 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  
 New 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.09 seconds. Powered By: Snitz Forums 2000