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

General SQL Server Forums

New to SQL Server Programming

Union All

Author

Topic

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2008-08-27 : 02:31:59

Hi,

I'm still a bit confuse with Union All. I have 4 tables:

1.

Level sesid Call Date
Client 2456789 8/12/2008
Client 1234567890 8/1/2008
Client 123456789 8/1/2008

2

sesid COUNT
2456789 1
1234567890 3
123456789 1

3.

sesid COUNT
123456789 1
1234567890 1
2456789 1

4.

sesid COUNT
2456789 2
1234567890 4
123456789 2

Want the table to be like this:

Email ID's Call Date # QA Raters # TL Raters # Raters
2456789 8/12/2008 1 1 2
1234567890 8/1/2008 3 1 4
123456789 8/1/2008 1 1 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 02:39:21

[code]SELECT t1.sesid,t1.CallDate,
SUM(CASE WHEN t.category='t2' THEN COUNT ELSE 0 END) AS [# QA Raters],
SUM(CASE WHEN t.category='t3' THEN COUNT ELSE 0 END) AS [# TL Raters],
SUM(CASE WHEN t.category='t4' THEN COUNT ELSE 0 END) AS [# Raters]
FROM Table1 t1
INNER JOIN
(SELECT sesid, COUNT,'t2' AS category
FROm Table2
UNION ALL
SELECT sesid, COUNT,'t3' AS category
FROm Table3
UNION ALL
SELECT sesid, COUNT,'t4' AS category
FROm Table4
) t
ON t.sesid=t1.sesid
GROUP BY t1.sesid,t1.CallDate[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-27 : 02:39:36

Try

SELECT		t1.SesID,
		t1.CallDate,
		SUM(COALESCE(w.QA, 0)) AS QA,
		SUM(COALESCE(w.TL, 0)) AS TL,
		SUM(COALESCE(w.R, 0)) AS R
FROM		Table1 AS t1
LEFT JOIN	(
			SELECT	SesID,
				Cnt AS QA,
				0 AS TL,
				0 AS R
			FROM	Table2

			UNION ALL

			SELECT	SesID,
				0,
				Cnt,
				0
			FROM	Table3

			UNION ALL

			SELECT	SesID,
				0,
				0,
				Cnt
			FROM	Table4
		) AS w ON w.SesID = t1.SesID
GROUP BY	t1.SesID,
		t1.CallDate

E 12°55'05.25"
N 56°04'39.16"

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-08-27 : 02:44:06

I feel you can get your desired result using join.

select table1.sesid as [IDS],table1.date as [Date],table2.Count as [QA Raters], table3.count as [TL Raters], table4.count as [Raters] from table1 Inner Join table2 on table1.SessId=table2.sessId Inner join
table3 on table2.sessId=table3.SessId inner join table4 on table3.sessid=table4.sessId

Hope you are looking for this.

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2008-08-27 : 02:47:51

I tried it:

SELECT CalibDell_DataCapture.SesID,
CalibDell_DataCapture.CallDate,
SUM(COALESCE(w.QA, 0)) AS QA,
SUM(COALESCE(w.TL, 0)) AS TL,
SUM(COALESCE(w.R, 0)) AS R
FROM CalibDell_DataCapture AS t1
LEFT JOIN (
SELECT SesID,
Cnt AS QA,
0 AS TL,
0 AS R
FROM CalibDell_QARaters

UNION ALL

SELECT SesID,
0,
Cnt,
0
FROM CalibDell_TLRaters

UNION ALL

SELECT SesID,
0,
0,
Cnt
FROM CalibDell_TotalRaters
) AS w ON w.SesID = CalibDell_DataCapture.SesID
GROUP BY CalibDell_DataCapture.SesID,
CalibDell_DataCapture.CallDate

I'm getting this message:

Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'CalibDell_DataCapture' does not match with a table name or alias name used in the query.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-27 : 02:49:41

See Peso's query

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 02:49:52

quote:
Originally posted by BankOfficerHere

I tried it:

SELECT t1.SesID,
t1.CallDate,
SUM(COALESCE(w.QA, 0)) AS QA,
SUM(COALESCE(w.TL, 0)) AS TL,
SUM(COALESCE(w.R, 0)) AS R
FROM CalibDell_DataCapture AS t1
LEFT JOIN (
SELECT SesID,
Cnt AS QA,
0 AS TL,
0 AS R
FROM CalibDell_QARaters

UNION ALL

SELECT SesID,
0,
Cnt,
0
FROM CalibDell_TLRaters

UNION ALL

SELECT SesID,
0,
0,
Cnt
FROM CalibDell_TotalRaters
) AS w ON w.SesID = t1.SesID
GROUP BY t1.SesID,
t1.CallDate

I'm getting this message:

Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'CalibDell_DataCapture' does not match with a table name or alias name used in the query.

change all occurances of it to t1 as you've defined it as alis.

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2008-08-27 : 02:51:07

sorry..i'm not sure how

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-27 : 02:52:49

quote:
Originally posted by BankOfficerHere

sorry..i'm not sure how

See my first reply

Madhivanan

Failing to plan is Planning to fail

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2008-08-27 : 02:59:13

I simplified the tables: here are they:

sesid emldte COUNT
2456789 8/12/2008 1
1234567890 1
1234567890 8/1/2008 2
123456789 8/1/2008 1

sesid emldte COUNT
123456789 8/1/2008 1
1234567890 8/1/2008 1
2456789 8/12/2008 1

sesid emldte COUNT
2456789 8/12/2008 2
1234567890 1
1234567890 8/1/2008 3
123456789 8/1/2008 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 03:05:43

quote:
Originally posted by BankOfficerHere

sorry..i'm not sure how

see code given by me along with the suggestion.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

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

[code]SELECT t1.SesID,
t1.CallDate,
SUM(COALESCE(w.QA, 0)) AS QA,
SUM(COALESCE(w.TL, 0)) AS TL,
SUM(COALESCE(w.R, 0)) AS R
FROM CalibDell_DataCapture AS t1
LEFT JOIN (
SELECT SesID,
[Count] AS QA,
0 AS TL,
0 AS R
FROM CalibDell_QARaters

UNION ALL

SELECT SesID,
0,
[Count],
0
FROM CalibDell_TLRaters

UNION ALL

SELECT SesID,
0,
0,
[Count]
FROM CalibDell_TotalRaters
) AS w ON w.SesID = t1.SesID
GROUP BY t1.SesID,
t1.CallDate[/code]

E 12°55'05.25"
N 56°04'39.16"

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2008-08-27 : 03:14:13

thanks peso..I'm getting it slowly by slowly.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-27 : 03:16:09

It's called ALIAS.
They are very handy. :-)

E 12°55'05.25"
N 56°04'39.16"

BankOfficerHere
Posting Yak Master

124 Posts

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

Sorry for another question: same issue on Union all.

Here's my table:

ID1 ID2 ID3 Date1 Date2 Date3
12 123 1234 2-Apr 2-Aug 3-May

I want the results to be like this:

ID's Dates
12 2-Apr
123 2-Aug
1234 3-May

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 04:32:14

[code]SELECT ID1,Date1
FROm YourTable
UNION ALL
SELECT ID2,Date2
FROm YourTable
UNION ALL
SELECT ID3,Date3
FROm YourTable[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources