Help with combining resultset from multiple tables

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

Help with combining resultset from multiple tables

Author

Topic

jhorten2011
Starting Member

14 Posts

Posted - 2011-12-10 : 16:20:54

I really need some help combining the results from 3 tables into a single resultset. I've tried using UNION, but I get the following error.

"ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator."

With my following query I get the results as 3 separate resultsets like below:

Sun 4274
Mon 13871
Tue 24803
Wed 12366
Thu 12412
Fri 11639
Sat 6654

Sun 5585
Mon 17766
Tue 32152
Wed 16074
Thu 15824
Fri 15690
Sat 8629

Sun 719
Mon 2207
Tue 4494
Wed 2123
Thu 2150
Fri 2313
Sat 1153

What I need is to get the resultset like below.

Day Table1 Table2 Table3
Sun 4274 5585 719
Mon 13871 17766 2207
Tue 24803 32152 4494
Wed 12366 16074 2123
Thu 12412 15824 2150
Fri 11639 15690 2313
Sat 6654 8629 1153

Can someone please help me with my query below to get the results above?

SELECT LEFT(DATENAME(weekday,[TimeStamp]), 3) AS [Day], COUNT(*) AS [Total Volume Table1]
FROM CLAIMS.Table1
WHERE [TimeStamp] BETWEEN CAST('2011-03-01 00:00:00.000' AS DATETIME) AND CAST('2011-03-08 23:59:59.999' AS DATETIME)
GROUP BY DATEPART(weekday,[TimeStamp]), DATENAME(weekday,[TimeStamp])
ORDER BY DATEPART(weekday,[TimeStamp]), DATENAME(weekday,[TimeStamp]) DESC

SELECT LEFT(DATENAME(weekday,[TimeStamp]), 3) AS [Day], COUNT(*) AS [Total Volume Table2]
FROM CLAIMS.Table2
WHERE [TimeStamp] BETWEEN CAST('2011-03-01 00:00:00.000' AS DATETIME) AND CAST('2011-03-08 23:59:59.999' AS DATETIME)
GROUP BY DATEPART(weekday,[TimeStamp]), DATENAME(weekday,[TimeStamp])
ORDER BY DATEPART(weekday,[TimeStamp]), DATENAME(weekday,[TimeStamp]) DESC

SELECT LEFT(DATENAME(weekday,[TimeStamp]), 3) AS [Day], COUNT(*) AS [Total Volume Table3]
FROM CLAIMS.Table3
WHERE [TimeStamp] BETWEEN CAST('2011-03-01 00:00:00.000' AS DATETIME) AND CAST('2011-03-08 23:59:59.999' AS DATETIME)
GROUP BY DATEPART(weekday,[TimeStamp]), DATENAME(weekday,[TimeStamp])
ORDER BY DATEPART(weekday,[TimeStamp]), DATENAME(weekday,[TimeStamp]) DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-11 : 01:09:03

[code]
SELECT LEFT([Day],3) AS [Day],SUM([Total Volume Table1]) AS [Total Volume Table1],SUM([Total Volume Table2]) AS [Total Volume Table2],SUM([Total Volume Table3]) AS [Total Volume Table3]
FROM
(
SELECT DATEPART(weekday,[TimeStamp]) AS [DayNo],DATENAME(weekday,[TimeStamp]) AS [Day], COUNT(*) AS [Total Volume Table1],CAST(NULL AS int) AS [Total Volume Table2],CAST(NULL AS int) AS [Total Volume Table3]
FROM CLAIMS.Table1
WHERE [TimeStamp] BETWEEN CAST('2011-03-01 00:00:00.000' AS DATETIME) AND CAST('2011-03-08 23:59:59.999' AS DATETIME)
GROUP BY DATEPART(weekday,[TimeStamp]), DATENAME(weekday,[TimeStamp])
UNION ALL
SELECT DATEPART(weekday,[TimeStamp]),DATENAME(weekday,[TimeStamp]),NULL, COUNT(*) ,NULL
FROM CLAIMS.Table2
WHERE [TimeStamp] BETWEEN CAST('2011-03-01 00:00:00.000' AS DATETIME) AND CAST('2011-03-08 23:59:59.999' AS DATETIME)
GROUP BY DATEPART(weekday,[TimeStamp]), DATENAME(weekday,[TimeStamp])
UNION ALL
SELECT DATEPART(weekday,[TimeStamp]),DATENAME(weekday,[TimeStamp]),NULL,NULL, COUNT(*)
FROM CLAIMS.Table3
WHERE [TimeStamp] BETWEEN CAST('2011-03-01 00:00:00.000' AS DATETIME) AND CAST('2011-03-08 23:59:59.999' AS DATETIME)
GROUP BY DATEPART(weekday,[TimeStamp]), DATENAME(weekday,[TimeStamp])
)t
GROUP BY [DayNo],[Day]
ORDER BY [DayNo], [Day] DESC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jhorten2011
Starting Member

14 Posts

Posted - 2011-12-11 : 09:15:18

Thank you so much, that is exactly what I was trying to achieve.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-11 : 11:11:45

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources