Ad:

MS SQL Discussion Thread
Marked Solved
Views: 1531

Jul 11th, 2008

Problems Joining Query

Expand Post »

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

MS SQL Syntax (Toggle Plain Text)
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
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

MS SQL Syntax (Toggle Plain Text)
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
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

Similar Threads

PHP newbie, project feasibility in PHP

bartbauldry

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Jul 2008

Permalink

Jul 11th, 2008

Re: Problems Joining Query

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

SELECT xxxxxx UNION SELECT yyyyyy

khess

Reputation Points: 94

Solved Threads: 8

Practically a Master Poster

Offline

638 posts
since Apr 2008

Permalink

Jul 11th, 2008

Re: Problems Joining Query

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:

MS SQL Syntax (Toggle Plain Text)
hourStamp   HourAverage HourToday   
----------- ----------- ----------- 
         50          52          
         46          34          
         40          39          
         41          52          
         39          46          
         44          33          
         43          51          
         42          43          
         53          49          
         62          66          
        69          56          
        76          64          
        66          55          
        73          65          
        73          16          
        81          0           
        76          0           
        64          0           
        70          0           
        69          0           
        67          0           
        69          0           
        58          0           
        50          0
hourStamp   HourAverage HourToday   
----------- ----------- ----------- 
         50          52          
         46          34          
         40          39          
         41          52          
         39          46          
         44          33          
         43          51          
         42          43          
         53          49          
         62          66          
        69          56          
        76          64          
        66          55          
        73          65          
        73          16          
        81          0           
        76          0           
        64          0           
        70          0           
        69          0           
        67          0           
        69          0           
        58          0           
        50          0

And the Latter returns:

MS SQL Syntax (Toggle Plain Text)
hourstamp   ActualVisits 
----------- ------------ 
         5            
         3            
         2            
         3            
         1            
         6            
         3            
         2            
         6            
         14           
        14           
        24           
        13           
        15           
        4
hourstamp   ActualVisits 
----------- ------------ 
         5            
         3            
         2            
         3            
         1            
         6            
         3            
         2            
         6            
         14           
        14           
        24           
        13           
        15           
        4

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

Thanks

bartbauldry

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Jul 2008

Permalink

Jul 11th, 2008

Re: Problems Joining Query

Try this:

SQL Syntax (Toggle Plain Text)
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
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.

cmhampton

Reputation Points: 23

Solved Threads: 10

Junior Poster in Training

Offline

79 posts
since Feb 2008

Permalink

Jul 11th, 2008

Re: Problems Joining Query

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)

MS SQL Syntax (Toggle Plain Text)
hourStamp   ActualVisits HourAverage HourToday   
----------- ------------ ----------- ----------- 
         <NULL>       50          52          
         5            <NULL>      <NULL>      
         <NULL>       46          34          
         3            <NULL>      <NULL>      
         <NULL>       40          39          
         2            <NULL>      <NULL>      
         <NULL>       41          52          
         3            <NULL>      <NULL>      
         <NULL>       39          46          
         1            <NULL>      <NULL>      
         <NULL>       44          33          
         6            <NULL>      <NULL>      
         <NULL>       43          51          
         3            <NULL>      <NULL>      
         <NULL>       42          43          
         2            <NULL>      <NULL>      
         <NULL>       53          49          
         6            <NULL>      <NULL>      
         <NULL>       62          66          
         14           <NULL>      <NULL>      
        <NULL>       69          56          
        14           <NULL>      <NULL>      
        <NULL>       76          64          
        24           <NULL>      <NULL>      
        <NULL>       66          55          
        13           <NULL>      <NULL>      
        <NULL>       73          65          
        15           <NULL>      <NULL>      
        <NULL>       75          51          
        16           <NULL>      <NULL>      
        <NULL>       84          85          
        21           <NULL>      <NULL>      
        <NULL>       80          78          
        17           <NULL>      <NULL>      
        <NULL>       67          49          
        5            <NULL>      <NULL>      
        <NULL>       70          0           
        <NULL>       69          0           
        <NULL>       67          0           
        <NULL>       69          0           
        <NULL>       58          0           
        <NULL>       50          0
hourStamp   ActualVisits HourAverage HourToday   
----------- ------------ ----------- ----------- 
         <NULL>       50          52          
         5            <NULL>      <NULL>      
         <NULL>       46          34          
         3            <NULL>      <NULL>      
         <NULL>       40          39          
         2            <NULL>      <NULL>      
         <NULL>       41          52          
         3            <NULL>      <NULL>      
         <NULL>       39          46          
         1            <NULL>      <NULL>      
         <NULL>       44          33          
         6            <NULL>      <NULL>      
         <NULL>       43          51          
         3            <NULL>      <NULL>      
         <NULL>       42          43          
         2            <NULL>      <NULL>      
         <NULL>       53          49          
         6            <NULL>      <NULL>      
         <NULL>       62          66          
         14           <NULL>      <NULL>      
        <NULL>       69          56          
        14           <NULL>      <NULL>      
        <NULL>       76          64          
        24           <NULL>      <NULL>      
        <NULL>       66          55          
        13           <NULL>      <NULL>      
        <NULL>       73          65          
        15           <NULL>      <NULL>      
        <NULL>       75          51          
        16           <NULL>      <NULL>      
        <NULL>       84          85          
        21           <NULL>      <NULL>      
        <NULL>       80          78          
        17           <NULL>      <NULL>      
        <NULL>       67          49          
        5            <NULL>      <NULL>      
        <NULL>       70          0           
        <NULL>       69          0           
        <NULL>       67          0           
        <NULL>       69          0           
        <NULL>       58          0           
        <NULL>       50          0

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

Thanks

bartbauldry

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Jul 2008

Permalink

Jul 11th, 2008

Re: Problems Joining Query

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.

cmhampton

Reputation Points: 23

Solved Threads: 10

Junior Poster in Training

Offline

79 posts
since Feb 2008

Permalink

Jul 14th, 2008

Re: Problems Joining Query

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

Last edited by bartbauldry; Jul 14th, 2008 at 4:51 am.

bartbauldry

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Jul 2008

Permalink

Jul 14th, 2008

Re: Problems Joining Query

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

(Toggle Plain Text)

SELECT t1.*, t2.actualVisits
FROM (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) t1

LEFT OUTER JOIN 

(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) t2 ON t1.hourStamp=t2.hourStamp

ORDER BY t1.hourStamp

wujtehacjusz

Reputation Points: 12

Solved Threads: 7

Newbie Poster

Offline

18 posts
since Mar 2007

Permalink

Jul 14th, 2008

Re: Problems Joining Query

It works!

Thanks to everyone for their help.
Thanks again

Bart

bartbauldry

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Jul 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Delete top 5 rows from a table

Next Thread in MS SQL Forum Timeline: deleting rows

DaniWeb Message

Cancel Changes

User Name
Password