Problem with Count and Null Values

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

Problem with Count and Null Values

Author

Topic

Alexd91
Starting Member

2 Posts

Posted - 2014-01-11 : 16:47:06

Hello,

I joined different tables and got a result like this:

result | process | goal | date |
------- ---------- ------ -----------
ok | process4 | 1 | 12.10.2013
bad | process1 | 2 | 13.10.2013
ok | process1 | 4 | 12.12.2013
good | process4 | 1 | 03.01.2014
ok | process1 | 3 | 10.04.2013
bad | process3 | 6 | 09.01.2014
bad | process4 | 3 | 30.12.2013
best |NULL| NULL

Now I want to count the results by counting the processes and group them by the result.

But it should be count the latest result per process only, e.g. for goal "1" just "good" at 03.01.2014. I solved that with a subquery (date=SELECT MAX(...)..).

But now the result "best" disappears, because that column has no date.

Secondly I want to count results for a specific process, e.g. for process4. Every goal has max. one process, with different dates. But one process could have more than one goal.

I want to have this result for process4:
count | result
------ -------
1 | good
1 | bad
0 | ok
0 | best

But I got only:
count | result
------ -------
1 | good
1 | bad

I have tried a lot, but nothing works.

The whole result (best, good, ok, bad) are stored in an other table and I joined it.

Could somene help me?

It would be very great :)

Thanks,
Alex

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-12 : 01:37:12

Can you explain your output. How did you get 0 for on for process4? you've record below
ok | process4 | 1 | 12.10.2013
so shouldnt it be 1 for ok?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Alexd91
Starting Member

2 Posts

Posted - 2014-01-12 : 07:02:53

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-12 : 14:20:20

quote:
Originally posted by Alexd91

No, because it should only count the latest date and result for each goal.
For goal = 1:
ok | process4 | 1 | 12.10.2013
good | process4 | 1 | 03.01.2014

And 03.01.2014 > 12.10.2013 so, +1 for good.

But that works fine, i just want to have the result with 0 when count=0.

Sorry I dont understand . In first post you specified you run it for each process. Can you tell me how you got bad as 1? bad for process4 is on 30.12.2013 which is not latest date.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

IleanaAlvare
Starting Member

7 Posts

Posted - 2014-01-31 : 09:59:21

Hey Alex,

Did you find a way to solve this problem?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources