SQL QUERY - 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

General SQL Server Forums

New to SQL Server Programming

SQL QUERY

Author

Topic

harikiranjammul
Starting Member

2 Posts

Posted - 2011-11-28 : 14:34:14

I need a small help on SQL query.

I have two tables RESPONSETIME00 & RESPONSETIME02, I need to count the URL’s such that how many times it was requested. I am attaching two tables & the output which I am looking for. Let me know how to get the attached output from the two tables.

Table 1 :

ID1 Node_Name ID4 ID7
10.150.3.1 SERVER5 [28/Nov/11:23:09:10 /i3-soft/com
10.150.3.1 SERVER3 [28/Nov/11:23:09:10 /reuest-transfer
10.150.3.1 SERVER1 [28/Nov/11:23:09:10 /reuest-transfer
10.150.3.1 SERVER1 [28/Nov/11:23:09:10 /reuest-transfer
10.150.3.1 SERVER1 [28/Nov/11:22:09:10 /reuest-transfer
10.150.3.1 SERVER8 [28/Nov/11:23:09:10 /i3-soft/com
10.150.3.1 SERVER1 [28/Nov/11:23:09:10 /i3-soft/com
10.150.3.1 SERVER1 [28/Nov/11:15:09:10 /i3-soft/com
10.150.3.1 SERVER6 [28/Nov/11:17:09:10 /i3-soft/com
10.150.3.1 SERVER5 [28/Nov/11:23:09:10 /i3-soft/com
10.150.3.1 SERVER1 [28/Nov/11:23:09:10 /reuest-transfer
10.150.3.1 SERVER1 [28/Nov/11:17:09:10 /reuest-transfer
10.150.3.1 SERVER3 [28/Nov/11:23:09:10 /reuest-transfer
10.150.3.1 SERVER1 [28/Nov/11:17:09:10 /reuest-transfer

Table 2 :

ID1 Node_Name ID4 ID7
10.150.3.1 SERVER1 [28/Nov/11:15:09:10 /i3-soft/com
10.150.3.1 SERVER3 [28/Nov/11:15:09:10 /reuest-transfer
10.150.3.1 SERVER1 [28/Nov/11:17:09:10 /reuest-transfer
10.150.3.1 SERVER1 [28/Nov/11:17:09:10 /reuest-transfer
10.150.3.1 SERVER1 [28/Nov/11:22:09:10 /reuest-transfer
10.150.3.1 SERVER4 [28/Nov/11:23:09:10 /i3-soft/com
10.150.3.1 SERVER1 [28/Nov/11:23:09:10 /i3-soft/com
10.150.3.1 SERVER1 [28/Nov/11:15:09:10 /i3-soft/com
10.150.3.1 SERVER6 [28/Nov/11:17:09:10 /i3-soft/com
10.150.3.1 SERVER1 [28/Nov/11:23:09:10 /i3-soft/com
10.150.3.1 SERVER1 [28/Nov/11:23:09:10 /i3-soft/com
10.150.3.1 SERVER1 [28/Nov/11:17:09:10 /i3-soft/com
10.150.3.1 SERVER3 [28/Nov/11:23:09:10 /i3-soft/com
10.150.3.1 SERVER1 [28/Nov/11:17:09:10 /i3-soft/com

output i am looking for

ID7 Count
/i3-soft/com 16
/reuest-transfer 12

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-11-28 : 22:40:09

[code]
SELECT ISNULL(T00.ID7, T02.ID7) AS ID7,
ISNULL(T00.[Count], 0) + ISNULL(T02.[Count], 0) AS [Count]
FROM (SELECT ID7, COUNT(*) AS [Count]
FROM RESPONSETIME00
GROUP BY ID7) AS T00
FULL OUTER JOIN
(SELECT ID7, COUNT(*) AS [Count]
FROM RESPONSETIME02
GROUP BY ID7) AS T02
ON T02.ID7 = T00.ID7
[/code]

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

sureshkk
Starting Member

21 Posts

Posted - 2011-11-29 : 08:09:30

SELECT ID7,COUNT(*) [Count]
FROM
(
SELECT ID7 FROM RESPONSETIME00
UNION ALL
SELECT ID7 FROM RESPONSETIME02
) R
GROUP BY ID7

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources