Help with Union - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Union

Author

Topic

rob196
Starting Member

9 Posts

Posted - 2009-02-04 : 09:16:45

Hi,

I hope this hasn't been asked before

I have this, it gives a name, Number1 is number of names in DONE state, Number2 is total number in table

It works fine, but i want to add in an error description (get it from another table) if Number2 > Number1, how do i do this?

I can get the error description with this SELECT ErrDesc FROM ErrorTbl WHERE (eCode = 1)

Select nm as Name, ISNULL(Sum(Number1), 0) As Num1, ISNULL(Sum(Number2), 0) As Num2
From (
Select TableA.Nm As nm, Count(TableA.Nm) As Number1, 0 As Number2 from TableA Where Status in ('DONE') Group By TableA.Nm
UNION ALL
Select TableA.Nm As nm, 0, Count(TableA.Nm) As Number2 from TableA Where TableA.Nm in (Select Distinct TableA.Nm from TableA) Group By TableA.Nm
) as j
Group By j.nm

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 09:22:07

your posted query doesnt make sense. in both queries separated by UNION ALL you're grouping by Nm field and taking count of same field. this will always give you 1. is this what you want?

rob196
Starting Member

9 Posts

Posted - 2009-02-04 : 09:29:57

yes it is, multiple items can have the same name (it gives me a summary per name)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 09:36:55

i think what you need is just this


SELECT t.Name,t.Number1,t.Number2 ,t2.Error_Description,..
FROM
(
Select TableA.Nm As Name, 
Sum(CASE WHEN Status ='DONE' THEN 1 ELSE 0 END) As Number1, 
COUNT(*) As Number2 
from TableA 
Group By TableA.Nm
)t
JOIN table2 t2
ON....

rob196
Starting Member

9 Posts

Posted - 2009-02-04 : 09:53:26

Works thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 09:54:39

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources