Stuck with Sub Query - SQL Server Forums

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

Stuck with Sub Query

Author

Topic

bentham7246
Starting Member

6 Posts

Posted - 2015-03-19 : 06:45:23

Hi,

I've been playing around with this for ages now and can't seem to find a solution. (I am still a beginner)

I know how to get to an answer the long way round but would like to know if there is a single query (with sub queries) that can solve this.

Want I would like to see in the result is a table that is grouped by source ID and counts the number of NULL and Zero records of a particular field in two separate columns.

So 3 columns in total. 1st Column would list the source ID so A, B, C, D etc (field name is feed_name).

Second column would count the number of records in that same table that have a NULL within the book value field (so book_value is NULL)

Third column would be the same as above but counting the zero records. (so book_value = 0)

A sample result would look like this:

Source | NULL BookUSD Records | Zero BookUSD Records
A 20 5
B 1 45
C 0 12
D 5 40
.....

I can get to a solution by using two separate queries and then using an UNION all function but i'm trying to make my scripts more efficient (and would also like to advance my skills)

This is my script so far:

select feed_name, COUNT(qrm_mart_id) as [NULL Records]
from t_alm_qrm_mart
where book_value is NULL
group by feed_name

union all

select feed_name, COUNT(qrm_mart_id) as [Zero Records]
from t_alm_qrm_mart
where book_value = 0
group by feed_name

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-03-19 : 06:56:03

Try this

select feed_name,
SUM(case when book_value is NULL then 1 else 0 end) as [NULL Records],
SUM(case when book_value=0 then 1 else 0 end) as [ZERO Records]
from t_alm_qrm_mart
group by feed_name

Madhivanan

Failing to plan is Planning to fail

bentham7246
Starting Member

6 Posts

Posted - 2015-03-19 : 07:01:09

Thanks very much. Works perfectly :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources