equentin
asked on
SQL SubSelect Query, join two separate queries
I've got two queries that I want to combine into one:
SELECT LogDay, Visits FROM vwNumVisitsByDay WHERE LogYear = 2007 AND LogMonth = 2 ORDER BY LogDay
SELECT LogDay, Pages FROM vwNumPagesByDay WHERE LogYear = 2007 AND LogMonth = 2 ORDER BY LogDay
The first query returns:
1 77
2 93
3 9
4 15
5 83
6 82
7 99
8 90
9 93
10 20
11 14
12 166
13 127
14 125
15 105
16 124
17 11
18 16
19 94
20 95
21 100
22 101
23 77
24 14
25 3
26 91
27 63
28 76
The second,
1 257
2 349
3 24
4 36
5 236
6 334
7 325
8 347
9 308
10 39
11 49
12 416
13 364
14 10
16 174
17 19
18 51
19 259
20 151
21 166
22 415
23 252
24 55
25 6
26 273
27 259
28 162
So I want the results instead to be:
1 77 257
2 93 349
3 9 24
4 15 36
5 83 236
6 82 334
7 99 325
8 90 347
9 93 308
10 20 39
11 14 49
12 166 416
13 127 364
14 125 10
15 105 0
16 124 174
17 11 19
18 16 51
19 94 259
20 95 151
21 100 166
22 101 415
23 77 252
24 14 55
25 3 6
26 91 273
27 63 259
28 76 162
Many thanks!
eq
SELECT LogDay, Visits FROM vwNumVisitsByDay WHERE LogYear = 2007 AND LogMonth = 2 ORDER BY LogDay
SELECT LogDay, Pages FROM vwNumPagesByDay WHERE LogYear = 2007 AND LogMonth = 2 ORDER BY LogDay
The first query returns:
1 77
2 93
3 9
4 15
5 83
6 82
7 99
8 90
9 93
10 20
11 14
12 166
13 127
14 125
15 105
16 124
17 11
18 16
19 94
20 95
21 100
22 101
23 77
24 14
25 3
26 91
27 63
28 76
The second,
1 257
2 349
3 24
4 36
5 236
6 334
7 325
8 347
9 308
10 39
11 49
12 416
13 364
14 10
16 174
17 19
18 51
19 259
20 151
21 166
22 415
23 252
24 55
25 6
26 273
27 259
28 162
So I want the results instead to be:
1 77 257
2 93 349
3 9 24
4 15 36
5 83 236
6 82 334
7 99 325
8 90 347
9 93 308
10 20 39
11 14 49
12 166 416
13 127 364
14 125 10
15 105 0
16 124 174
17 11 19
18 16 51
19 94 259
20 95 151
21 100 166
22 101 415
23 77 252
24 14 55
25 3 6
26 91 273
27 63 259
28 76 162
Many thanks!
eq
lahousden
SELECT LogDay, Visits, Pages FROM vwNumVisitsByDay WHERE LogYear = 2007 AND LogMonth = 2 ORDER BY LogDay
ASKER
They're actually two different views I'm using...
SELECT a.LogDay, SUM(a.Pages), SUM(a.Visits)
FROM (
SELECT LogDay, 0 Pages, Visits FROM vwNumVisitsByDay WHERE LogYear = 2007 AND LogMonth = 2 ORDER BY LogDay
Union All
SELECT LogDay, Pages, 0 FROM vwNumPagesByDay WHERE LogYear = 2007 AND LogMonth = 2 ORDER BY LogDay) a
GROUP BY a.LogDay
FROM (
SELECT LogDay, 0 Pages, Visits FROM vwNumVisitsByDay WHERE LogYear = 2007 AND LogMonth = 2 ORDER BY LogDay
Union All
SELECT LogDay, Pages, 0 FROM vwNumPagesByDay WHERE LogYear = 2007 AND LogMonth = 2 ORDER BY LogDay) a
GROUP BY a.LogDay
ASKER
Hi Ethan,
That returns the following error:
Server: Msg 1033, Level 15, State 1, Line 4
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near ')'.
That returns the following error:
Server: Msg 1033, Level 15, State 1, Line 4
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near ')'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT a.LogDay, SUM(a.Pages), SUM(a.Visits)
FROM (
SELECT LogDay, 0 Pages, Visits FROM vwNumVisitsByDay WHERE LogYear = 2007 AND LogMonth = 2
Union All
SELECT LogDay, Pages, 0 FROM vwNumPagesByDay WHERE LogYear = 2007 AND LogMonth = 2 ORDER BY LogDay) a
GROUP BY a.LogDay
ORDER BY a.LogDay
FROM (
SELECT LogDay, 0 Pages, Visits FROM vwNumVisitsByDay WHERE LogYear = 2007 AND LogMonth = 2
Union All
SELECT LogDay, Pages, 0 FROM vwNumPagesByDay WHERE LogYear = 2007 AND LogMonth = 2 ORDER BY LogDay) a
GROUP BY a.LogDay
ORDER BY a.LogDay
(...I know... ambiguous names...)
first line should be:
SELECT isnull (v.LogDay, pLogDay) LogDay, v.Visits, p.Pages
first line should be:
SELECT isnull (v.LogDay, pLogDay) LogDay, v.Visits, p.Pages
ASKER
That's great, thanks all for your help!