T-SQL Info - 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

SQL Server 2000 Forums

Transact-SQL (2000)

T-SQL Info

Author

Topic

spiralthus
Starting Member

6 Posts

Posted - 2004-06-10 : 00:21:27

Hi,

I have been building web apps for about 8 years, but never needed to do much complex SQL.

I have now been posed a question like follows. Normally I would do this as a few statements, or in the program code, but how do you get this in the SQL?

-------------------------------------------
Select out a list of customers who visited more than once on a given day, how many times they visited, and what their average height was.

dbo.visits
----------------------
visit_id INT
customer_id INT
height FLOAT
visit_datetime DATETIME
-------------------------------------------

My solution is as follows:
----

declare @Date datetime
set @Date = '4004-09-19'
select customer_id, count(customer_id) as "Number of Visits"
from visits
where cast(convert(char(10),visit_datetime, 101)as datetime) = @Date
group by cast(convert(char(10),visit_datetime, 101)as datetime), customer_id
having count(customer_id) > 1
order by count(customer_id) desc
----
But, I am having problems with the average height. I can take it in a subquery like this:

select customer_id, count(customer_id) as "Number of Visits" (select avg(height) from visits where cast(convert(char(10),visit_datetime, 101)as datetime) = @Date) as "Average Height"
from visits
where cast(convert(char(10),visit_datetime, 101)as datetime) = @Date
group by cast(convert(char(10),visit_datetime, 101)as datetime), customer_id
having count(customer_id) > 1
order by count(customer_id) desc

But the problem I have here is that Average Height now contains all visitors that day, and I need only the average height of the visitors who visited more than twice.

I would really like to master this concept (Think I am almost there), but don't quite get how to find the right dataset for the avg(height).

Thanks a lot for any input.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-10 : 01:00:23

SELECT Convert(varchar, visit_datetime, 3) as VisitDate, Customer_ID, Count(Visit_ID) as NumberOfVisits, AVG(height) as AvgHeight
FROM Visits
GROUP BY Convert(varchar, visit_datetime, 3), Customer_ID
HAVING Count(Visit_ID) > 1

Should do the trick

Tim

spiralthus
Starting Member

6 Posts

Posted - 2004-06-10 : 01:39:02

Thank you. That was close to what I though originally, but AvgHeight now contains the average height of "each visitor", not the average height of "all the visitors for the day who visited more than twice".

For instance, running this querry on a bogus data set I get:
date visitor_id Num Visits Average Height
01/08/04 2 23 2.0
01/09/04 3 23 3.0
01/09/04 99 23 99.0
02/08/04 1 23 1.0
02/08/04 2 23 2.0
02/09/02 777 2 777.0

where what I need is somehow to get the average of all visitors for 1/9/04, which would be something like 3+99/2 = 51.

Note that I have set the height of all visits to visitor_id * 10.

Am I way off here?

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-10 : 02:20:42

Just to clarify:

You want to return a maximum of 1 row per day?

spiralthus
Starting Member

6 Posts

Posted - 2004-06-10 : 02:32:49

One row per day, per visitor, who visited more than once, along with the number of visits per that visitor, and the average height of all visitors for that day, who visited more than once.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-10 : 02:44:29

SO - if in a particular day you have several visitors who visit more than once, each row for that day will have an identical average height?

spiralthus
Starting Member

6 Posts

Posted - 2004-06-10 : 02:51:23

Yes, the average height will be duplicated. It is the average height of visitors for THAT DAY who visited more than once.

Personally I would have made it a sepperate query to not have it duplicated, but this is what I am asked for, I think.

The problem I have, is that I can only get the average height of all visitors per day above, and don't know how to figure out how to constrain that to the ones who visitor more than once.

spiralthus
Starting Member

6 Posts

Posted - 2004-06-10 : 16:05:58

I ended up solving as follows, but I am interested to know if anyone can think of a better way.

declare @Date datetime
set @Date = '4004-09-19'

select customer_id, count(customer_id) as "Number of Visits", case when count(customer_id) < 10 then 'Nope' when count(customer_id) = 10 then 'Almost...' when count(customer_id) > 10 then 'Yes!' end as "More Than 10 Visits Today?"
from visits
where cast(convert(char(10),visit_datetime, 101)as datetime) = @Date
group by cast(convert(char(10),visit_datetime, 101)as datetime), customer_id
having count(customer_id) > 1
order by count(customer_id) desc

select avg(height) from visits where customer_id in (
select customer_id from visits
where cast(convert(char(10),visit_datetime, 101)as datetime) = @Date
group by customer_id
having count(customer_id) > 1
)

Note that:
- The second query is the average hieght OF VISITS BY visitors who visited more than once, not the average height of those visitors. So if a visitor's height changes, it will sway the average, and if a visitor visits a lot, they will weight more in the average. Thereason I didn't just select out avg(visits) above is that would only give me the average height of each visitor, not for the visits that day.

Anyone have any comments?

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-10 : 17:09:58

[code]
USE Northwind
GO

CREATE TABLE myTable99 (
visit_id INT IDENTITY(1,1)
, customer_id INT
, height FLOAT
, visit_datetime DATETIME
)
GO

INSERT INTO myTable99(
customer_id
, height
, visit_datetime
)
SELECT 1, 5.5, '1/1/2004 13:05:05' UNION ALL
SELECT 2, 5.9, '1/1/2004 13:06:05' UNION ALL
SELECT 3, 4.5, '1/1/2004 13:07:05' UNION ALL
SELECT 1, 5.5, '1/1/2004 14:05:05' UNION ALL
SELECT 3, 4.5, '1/1/2004 15:35:05' UNION ALL
SELECT 1, 5.5, '1/1/2004 14:05:05' UNION ALL
SELECT 1, 5.5, '1/1/2004 14:05:05' UNION ALL
SELECT 1, 5.5, '1/1/2004 14:05:05' UNION ALL
SELECT 1, 5.5, '1/1/2004 14:05:05' UNION ALL
SELECT 1, 5.5, '1/1/2004 16:05:05'
GO

DECLARE @x datetime, @y datetime

SELECT @x = '1/1/2004 00:00:00', @y = '1/1/2004 23:59:59'

-- The wrong Answer

SELECT AVG(height)
FROM myTable99
WHERE visit_datetime > @x
AND visit_datetime <= @y
AND customer_id
IN (SELECT customer_id
FROM myTable99 i
GROUP BY customer_Id
HAVING COUNT(*) > 1)

-- The right one, assuming they don't grow or shrink in a 24 hour period

SELECT AVG(height)
FROM (SELECT DISTINCT height
FROM myTable99
WHERE visit_datetime > @x
AND visit_datetime <= @y
AND customer_id
IN (SELECT customer_id
FROM myTable99 i
GROUP BY customer_Id
HAVING COUNT(*) > 1)
) AS XXX
GO

DROP TABLE myTable99
GO

[/code]

Brett

8-)

spiralthus
Starting Member

6 Posts

Posted - 2004-06-10 : 17:18:22

Aaah, very nice.

Thank you Brett. Does this forum have some sort of award system, or do I just leave the topic as is?

Thanks again.

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-11 : 10:42:28

quote:
Originally posted by spiralthus

Aaah, very nice.

Thank you Brett. Does this forum have some sort of award system, or do I just leave the topic as is?

Thanks again.

No, but if you're in the NYC Metro area, I'll have a margarita, rocks, no salt.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources