Problems Joining Query

[bartbauldry]

Hello I seem to be having a problem, I have this query that pulls back correct results

SELECT hourStamp, SUM(CASE WHEN datestamp < GETDATE() THEN 1 ELSE 0 END) / @numberofdays as HourAverage, SUM(CASE WHEN dateStamp BETWEEN DATEADD(d,DATEDIFF(d,0,GETDATE()),0) AND GETDATE() THEN 1 ELSE 0 END) as HourToday

FROM webstats

GROUP BY hourStamp
ORDER BY hourStamp

I also have this query that pulls back correct results

SELECT hourstamp, COUNT(id) AS ActualVisits
FROM webStats
WHERE id IN                            
(SELECT ws.id
FROM webStats AS ws INNER JOIN webStatsTrace AS wst ON ws.id = wst.statsid
WHERE (ws.datestamp BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AND GETDATE())
GROUP BY ws.id HAVING (COUNT(wst.statsid) > 1))
GROUP BY hourstamp
ORDER BY hourstamp

But i am unable to join the two querys, or rewrite them so i run one query and get all the results in one... could anyone please suggest a way?

Thanks
Bart

[khess]

You can easily join two queries with the UNION keyword between the two individual SELECT queries:

SELECT xxxxxx UNION SELECT yyyyyy

[bartbauldry]

Thanks, I have tried a UNION but it complains that there they do not have an equal number of expressions on their target list.

I have tried restructuing the query to no avail, the problem is that the fisrt query returns:

hourStamp   HourAverage HourToday   
----------- ----------- ----------- 
0           50          52          
1           46          34          
2           40          39          
3           41          52          
4           39          46          
5           44          33          
6           43          51          
7           42          43          
8           53          49          
9           62          66          
10          69          56          
11          76          64          
12          66          55          
13          73          65          
14          73          16          
15          81          0           
16          76          0           
17          64          0           
18          70          0           
19          69          0           
20          67          0           
21          69          0           
22          58          0           
23          50          0

And the Latter returns:

hourstamp   ActualVisits 
----------- ------------ 
0           5            
1           3            
2           2            
3           3            
4           1            
5           6            
6           3            
7           2            
8           6            
9           14           
10          14           
11          24           
12          13           
13          15           
14          4

The latter will add rows as the hours pass....
Any Ideas?

Thanks

[cmhampton]

Try this:

SELECT 
		hourStamp,
		NULL AS ActualVisits,
		SUM(CASE WHEN datestamp < GETDATE() THEN 1 ELSE 0 END) / @numberofdays as HourAverage, 
		SUM(CASE WHEN dateStamp BETWEEN DATEADD(d,DATEDIFF(d,0,GETDATE()),0) AND GETDATE() THEN 1 ELSE 0 END) as HourToday
FROM 
		webstats
GROUP BY 
		hourStamp

UNION

SELECT 
		hourstamp, 
		COUNT(id) AS ActualVisits,
		NULL AS HourAverage,
		NULL AS HourToday
FROM 
		webStats
WHERE 
		id IN                            
		(SELECT ws.id
		FROM webStats AS ws INNER JOIN webStatsTrace AS wst ON ws.id = wst.statsid
		WHERE (ws.datestamp BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AND GETDATE())
		GROUP BY ws.id HAVING (COUNT(wst.statsid) > 1))
GROUP BY 
		hourstamp
ORDER BY 
		hourstamp

Use aliased NULLs to make the SELECT clauses of the two queries match.

[bartbauldry]

Thanks, that has helped, its now returning its a whole but it seems to be duplicating rows of the hours that have already passed (if that makes sence)

hourStamp   ActualVisits HourAverage HourToday   
----------- ------------ ----------- ----------- 
0           <NULL>       50          52          
0           5            <NULL>      <NULL>      
1           <NULL>       46          34          
1           3            <NULL>      <NULL>      
2           <NULL>       40          39          
2           2            <NULL>      <NULL>      
3           <NULL>       41          52          
3           3            <NULL>      <NULL>      
4           <NULL>       39          46          
4           1            <NULL>      <NULL>      
5           <NULL>       44          33          
5           6            <NULL>      <NULL>      
6           <NULL>       43          51          
6           3            <NULL>      <NULL>      
7           <NULL>       42          43          
7           2            <NULL>      <NULL>      
8           <NULL>       53          49          
8           6            <NULL>      <NULL>      
9           <NULL>       62          66          
9           14           <NULL>      <NULL>      
10          <NULL>       69          56          
10          14           <NULL>      <NULL>      
11          <NULL>       76          64          
11          24           <NULL>      <NULL>      
12          <NULL>       66          55          
12          13           <NULL>      <NULL>      
13          <NULL>       73          65          
13          15           <NULL>      <NULL>      
14          <NULL>       75          51          
14          16           <NULL>      <NULL>      
15          <NULL>       84          85          
15          21           <NULL>      <NULL>      
16          <NULL>       80          78          
16          17           <NULL>      <NULL>      
17          <NULL>       67          49          
17          5            <NULL>      <NULL>      
18          <NULL>       70          0           
19          <NULL>       69          0           
20          <NULL>       67          0           
21          <NULL>       69          0           
22          <NULL>       58          0           
23          <NULL>       50          0

Ive tried to remove the duplicate rows but seem unable to, i thought the union option wouldnt allow duplicate rows?

Thanks

[cmhampton]

Can you give me a little more information about what this particular query is trying to do? You may not need to use UNION, but perhaps different JOINs.

[bartbauldry]

Hello,

The results I would like from the query is 4 columns, one with an hourstamp (0-23),

the next column with an average hourly hits (i have done as count each hour and / by number of days),

the next column with today’s hits so far,

and final column as the number of hits who have visited more than 1 page

If you would like me to post some sample data i can,

Thanks for your help
Bart

[wujtehacjusz]

I think that you need to join query one with query two... try:

SELECT t1.*, t2.actualVisits
FROM (
[INDENT]SELECT hourStamp, 
SUM(CASE WHEN datestamp < GETDATE() THEN 1 ELSE 0 END) / @numberofdays as HourAverage, 
SUM(CASE WHEN dateStamp BETWEEN DATEADD(d,DATEDIFF(d,0,GETDATE()),0) AND GETDATE() THEN 1 ELSE 0 END) as HourToday

FROM webstats

GROUP BY hourStamp[/INDENT]
) t1

LEFT OUTER JOIN 

(
[INDENT]SELECT hourstamp, COUNT(id) AS ActualVisits
FROM webStats
WHERE id IN                            
(SELECT ws.id
FROM webStats AS ws INNER JOIN webStatsTrace AS wst ON ws.id = wst.statsid
WHERE (ws.datestamp BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AND GETDATE())
GROUP BY ws.id HAVING (COUNT(wst.statsid) > 1))
GROUP BY hourstamp[/INDENT]
) t2 ON t1.hourStamp=t2.hourStamp

ORDER BY t1.hourStamp

[bartbauldry]

It works!

Thanks to everyone for their help.
Thanks again

Bart