Adding further columns to existing SQL 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 2005 Forums

Transact-SQL (2005)

Adding further columns to existing SQL Query.

Author

Topic

chrispatt45
Starting Member

4 Posts

Posted - 2009-09-16 : 09:01:03

Hi All,
I am having an issue getting the correct coding to complete a query I need to extract information from our network monitoring system.
Currently the query is:
SELECT TOP (25) dbo.DomainMonitor.URLID AS URL, COUNT(dbo.DomainMonitor.URLID) AS hits, dbo.URL.Name
FROM dbo.DomainMonitor INNER JOIN
dbo.URL ON dbo.DomainMonitor.URLID = dbo.URL.URLID
WHERE (dbo.DomainMonitor.StartTime > GETDATE() - 30)
GROUP BY dbo.DomainMonitor.URLID, dbo.URL.Name
ORDER BY hits DESC

Simply what this does is it returns a table showing the top25 visited URLS and the number of hits for each for the last 30 days, the table outputs as follows:

Name Hits
Eg facebook Eg 2000
Eg Hotmail Eg 1350

What I need is to have the table list also each departments contribution to these hits, so something like the following:

Name Department 1 Department 2 Department 3
Facebook 1500 300 200
Hotmail 1000 250 100

There are 2 more tables I think I need to include, these are Department and PC, they have structure/fields as follows:

Department:-
ID
URL ID
LOGGED ON USER ID

PC:-
ID
DEPARTMENT ID

If anyone could help, Id be very grateful, I have tried to include everything I think could be needed, but if you need more info, please dont hesitate to let me know.

Thanks

Chris Patterson

chrispatt45
Starting Member

4 Posts

Posted - 2009-09-16 : 11:14:34

Please ignore this message all, Ive just realised this is the 2005 SQL forum :/ Apologies.

Chris Patterson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources