Adding product of a subquery to a subquery fails

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

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding product of a subquery to a subquery fails

Author

Topic

student_4187
Starting Member

4 Posts

Posted - 2014-07-06 : 17:56:17

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:

[url]http://www.photo-host.org/Yr[/url]

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 - 2014-07-06 : 19:08:12

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-07 : 02:52:20

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

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

yado
Starting Member

8 Posts

Posted - 2014-07-07 : 03:33:38

unspammed

student_4187
Starting Member

4 Posts

Posted - 2014-07-07 : 23:26:13

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-08 : 05:19:08

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

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

student_4187
Starting Member

4 Posts

Posted - 2014-07-12 : 01:40:39

Thank you all for your help. I will continue to visit here as it has been very helpful.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources