Combining summary and detail information in the same query

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

SQL Server Development (2000)

Combining summary and detail information in the same query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-03-25 : 07:59:45

Imraan writes "Hi

I have two tables. The first table is called 'virtualURL' and stores a number of URLs. The second is called 'virutalHits' that stores all the hits to the virtualURLs. Every hit is one record in the virtualHits table.

I would like to combine the data in the two tables as follows. The query should select all the fields from virtualURL. I would like to add a column to that query called 'hits'. This column will contain the number of times the URL has been accessed (this comes from the virtualHits table).

Is it possible to get this result using SQL? How should I go about tackling this problem?

Windows Version: 2000 Server SP4
SQL Version: 2000 SP3"

ben_leah
Starting Member

3 Posts

Posted - 2004-03-25 : 08:11:16

SELECT a.*,(SELECT b.Count(*) FROM virtualURLs b WHERE b.URLName = a.URLName) as 'Hits'
FROM virtualURL a

ben_leah
Starting Member

3 Posts

Posted - 2004-03-25 : 08:12:06

quote:
Originally posted by ben_leah

SELECT a.*,(SELECT Count(b.*) FROM virtualURLs b WHERE b.URLName = a.URLName) as 'Hits'
FROM virtualURL a

even

ben_leah
Starting Member

3 Posts

Posted - 2004-03-25 : 08:19:02

OR .. probably a better way and faster

SELECT a.URL_Name,COUNT(*)
FROM virtualURL a INNER JOIN virtualURLs b ON a.URL_Name = b.URL_Name
GROUP BY a.URLName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources