union all - 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)

union all

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-10-27 : 07:20:05

I have three tables

Table: DryCD1
Fields: RGN, Area, Dist, PAN, Drycd1, Drydte1

Table DryCD2
Fields: RGN, Area, Dist, PAN, Drycd2, Drydte2

Table DryCD3
Fields: RGN, Area, Dist, PAN, Drycd3, Drydte3

How can I use the Union All to get the fields Drycd2, Drydte2, Drycd3 and Drydte3? Is this possible. Right now when I union all I only get RGN, Area, Dist, PAN, Drycd1 and Drydte.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 07:34:37

[code]SELECT RGN, Area, Dist, PAN, Drycd1, Drydte1,CASt(NULL AS datatype),CAST(NULL as datatype),cast(NULL as datatype),cast(NULL as datatype)
FROM DryCD1
UNION ALL
SELECT RGN, Area, Dist, PAN, NULL,NULL,Drycd2, Drydte2,NULL,NULL
FROM DryCD2
UNION ALL
SELECT RGN, Area, Dist, PAN, NULL,NULL,NULL,NULL,Drycd3, Drydte3
FROM DryCD3[/code]

remember to cast NULL as appropriate types of columns in first select

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-10-27 : 08:03:30

Thanks that did it but now I only want to get the counts from the three tables

Select count(*) as Pending
from Drycd1, Drycd2, Drycd3
where pan='164487'

Can I do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 08:06:47

quote:
Originally posted by JJ297

Thanks that did it but now I only want to get the counts from the three tables

Select count(*) as Pending
from Drycd1, Drycd2, Drycd3
where pan='164487'

Can I do this?

you can. ut what are fields on which you want to link the three tables? that should be specified else it will take cross join and will screw up the count value.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-10-27 : 08:14:07

I want to join all three tables on PAN to get the count of how many times PAN is listed in the three tables. Does that make sense.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 08:24:38

then is nt this enough?


SELECT PAN,
SUM(CASE WHEN Cat=1 THEN 1 ELSE 0 END) AS CountTable1,
SUM(CASE WHEN Cat=2 THEN 1 ELSE 0 END) AS CountTable2,
SUM(CASE WHEN Cat=3 THEN 1 ELSE 0 END) AS CountTable3,
COUNT(*) AS CountTotal
FROM
(
SELECT PAN,1 AS cat FROM DryCD1
UNION ALL
SELECT PAN,2 FROM DryCD2
UNION ALL
SELECT PAN,3 FROM DryCD3 
)t
GROUP BY PAN

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-10-27 : 08:37:30

Yes it is perfect! Thanks!

Now I want to break it down I have two dates in the table '20081003, and 20081010'. That column is called week_start_date. How to add a where clause for week_start_date = '20081003' to get a count from all three tables?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-10-27 : 12:12:17

I got it:

SELECT PAN,
SUM(CASE WHEN Cat=1 THEN 1 ELSE 0 END) AS CountTable1,
SUM(CASE WHEN Cat=2 THEN 1 ELSE 0 END) AS CountTable2,
SUM(CASE WHEN Cat=3 THEN 1 ELSE 0 END) AS CountTable3,
COUNT(*) AS CountTotal
FROM
(
SELECT PAN,1 AS cat
where week_start_date='20081003'
FROM DryCD1
UNION ALL
SELECT PAN,2
where week_start_date='20081003'
FROM DryCD2
UNION ALL
SELECT PAN,3
where week_start_date='20081003'
FROM DryCD3
)t
GROUP BY PAN

This gives me what I need. Thanks again for your assistance. Until next time...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 12:39:54

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources