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

General SQL Server Forums

New to SQL Server Programming

Adding further columns to existing SQL Query.

Author

Topic

chrispatt45
Starting Member

4 Posts

Posted - 2009-09-16 : 11:15:10

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

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-16 : 11:31:42

You can do something like:

RIGHT OUTER JOIN dbo.Department
ON dbo.DomainMonitor.URLid = dbo.Department.URLid

Will link all departments to each of your top 25.

Sorry to say not sure how you would bet each department as a column but this returns a new row for each URL/Department combination

John

"The smoke monster is just the Others doing barbecue"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-16 : 11:52:12

Perhaps something like this. This code assumes that dbo.Department correlates to DomainMonitor by url_id = urlid. Also that Department.ID contains values like 'Department 1'. If those assumptions are not correct then you'll need to fix this code.
It also assumes that you are using sql server 2005 or later and that the database compatibility level is set to at least 90.


select top 25 p.*
from   (
       SELECT 
              dm.URLID AS URL
              ,u.Name
              ,d.id
              ,COUNT(dm.URLID) AS hits
       FROM   dbo.DomainMonitor dm
       INNER JOIN dbo.URL u
              ON dm.URLID = u.URLID
       inner join dbo.Department d
              on d.url_id = dm.urlid 
       WHERE dm.StartTime > GETDATE() - 30
       GROUP BY dm.URLID
              ,u.Name
              ,d.id
       ) d
pivot (
sum(hits) for d.id in ([department 1], [department 2], [department 3])
) p
--ORDER BY COUNT(dm.URLID) DESC

EDIT:
actually, my order by won't work. Do you want to sort by the total hits for all departments together?

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-16 : 12:17:23

which table you've departments? will department names be static?

chrispatt45
Starting Member

4 Posts

Posted - 2009-09-17 : 03:33:05

Hi All, many thanks for your messages, unfortunately none have worked as yet,however, the reason for this is down to my stupidity :( I listed the wrong fields in the description given initially, so my apologies for anyones time wasted so far. Ill give a breakdown of the fields in each table.(Correct ones this time :) )

Domain Monitor //(Used in current query to return top 25 URLS total):-
ID (Unique client ID)
URL ID
LoggedOnUserID

URL:-
URLID
Name

Department:-
DepartmentID
Name

PC:-
ID(Unique client ID)
DEPARTMENT ID

Some how Id hoped to link department through Department ID to PC table, and then PC table to Domain monitor through ID(Unique identifier) and then the end result would be to combine the overall with my previous statement to give the departments in top 25 list also as follows

URL DEPT1 DEPT 2 DEPT3
(url nAME for top URL used) Hits for dept1 on the URL specified. "for Dept2" "for Dept3"

Just to recap, my previous code which worked fine was:

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 link the departments into this statement using the tables listed above
and give the hits for each department as their contribution to the overall hits per URL.

I hope that makes sense :) Apologies again for the initial mistake.

Chris Patterson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources