Help needed to combine 2 SQL scripts

SELECT PayloadFilterID,SourceIP, DestinationIP, Action, Severity, COUNT(1) from dbo.payloadlogs WHERE  (payloadlogs.LogDate >= '2015-02-01 00:00:00.0' AND payloadlogs.LogDate < '2015-03-01 00:00:00.0')  GROUP BY PayloadFilterID, SourceIP, DestinationIP, Action ORDER BY COUNT(1) DESC

The above SQL script is very close to what I needed but it's missing one more column/field,
ie payloadfilter2s.Name  found in the script below:

SELECT payloadlogs.PayloadLogID, payloadlogs.PayloadFilterID, payloadlogs.SourceIP, payloadlogs.DestinationIP, payloadlogs.action, payloadlogs.Severity, payloadfilter2s.PayloadFilter2ID, payloadfilter2s.TBUID, payloadfilter2s.Name FROM payloadlogs, payloadfilter2s WHERE payloadlogs.PayloadFilterID = payloadfilter2s.PayloadFilter2ID order by payloadlogs.PayloadLogID


I kept getting some sort of syntax error if I attempts to insert
"GROUP BY payloadfilter2s.Name, SourceIP, DestinationIP,  payloadlogs.action  ORDER BY COUNT(1) DESC"
   into the 2nd SQL (which if successfully implemented in the 2nd SQL will closely meet my requirements)


Let me know any further inputs/info needed from me to implement requirements indicated above
sunhuxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leo TorresSQL DeveloperCommented:
Try
SELECT PayloadFilterID,SourceIP, DestinationIP, Action, Severity, pl.Name ,COUNT(1) 
from dbo.payloadlogs pl	
	join payloadfilter2s pf
		on pl.PayloadFilterID = pf.PayloadFilter2ID
WHERE  (payloadlogs.LogDate >= '2015-02-01 00:00:00.0' 
AND payloadlogs.LogDate < '2015-03-01 00:00:00.0')  
GROUP BY PayloadFilterID, SourceIP, DestinationIP,pl.Name, Action 
ORDER BY COUNT(1) DESC

Open in new window

0
ManjuIT - Project ManagerCommented:
Any reason why not using Join query?

from your above statement, i believe u try to pull columns from 2 tables with a id common between both.

Ex:
SELECT payloadlogs.PayloadLogID, payloadlogs.PayloadFilterID, payloadfilter2s.PayloadFilter2ID,
payloadfilter2s.TBUID, payloadfilter2s.Name
FROM payloadlogs
INNER JOIN payloadfilter2s
ON payloadlogs.PayloadFilterID=payloadfilter2s.PayloadFilter2ID;

use Left / Right / Inner Join's - however you want your data to be seen..,

Cheers
0
Mike EghtebasDatabase and Application DeveloperCommented:
no points, consider having:

payloadlogs.LogDate >= '2015-02-01' AND payloadlogs.LogDate < '2015-03-01'
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can use CTE:
WITH CTE_Logs (PayloadFilterID, SourceIP, DestinationIP, Action, Severity, CountPayLoad)
AS (SELECT PayloadFilterID,SourceIP, DestinationIP, Action, Severity, COUNT(1) 
	FROM dbo.payloadlogs 
	WHERE  (payloadlogs.LogDate >= '2015-02-01 00:00:00.0' AND payloadlogs.LogDate < '2015-03-01 00:00:00.0')  
	GROUP BY PayloadFilterID, SourceIP, DestinationIP, Action)

SELECT CTE_Logs.CountPayLoad, CTE_Logs.PayloadFilterID, CTE_Logs.SourceIP, CTE_Logs.DestinationIP, CTE_Logs.action, 
		CTE_Logs.Severity, payloadfilter2s.PayloadFilter2ID, payloadfilter2s.TBUID, payloadfilter2s.Name 
FROM CTE_Logs
	INNER JOIN payloadfilter2s WHERE CTE_Logs.PayloadFilterID = payloadfilter2s.PayloadFilter2ID 
ORDER BY CTE_Logs.CountPayLoad DESC

Open in new window

0
Scott PletcherSenior DBACommented:
Do the name lookup last by making the first query a derived table:

SELECT ppl2.*, plf2.PayloadFilter2ID, plf2.TBUID, plf2.Name
FROM (
    SELECT pll.PayloadFilterID, pll.SourceIP, pll.DestinationIP, pll.Action, pll.Severity, COUNT(1) AS [Count]
    FROM dbo.payloadlogs AS pll
    WHERE  (pll.LogDate >= '20150201' AND pll.LogDate < '20150301')
    GROUP BY pll.PayloadFilterID, pll.SourceIP, pll.DestinationIP, pll.Action
) AS pll2
INNER JOIN dbo.payloadfilter2s AS plf2 ON
    pll2.PayloadFilterID = plf2.PayloadFilter2ID
ORDER BY pll2.PayloadLogID
0
sunhuxAuthor Commented:
thanks very much, will test it out tmr

Yes eghtebas, will need to bound the start & end dates.

Suppose it's now Apr '15,  can anyone amend it such that the
  start date is  1st_of_Last_Month:00:00hrs  &
  end date is 1st_of_This_Month:00:00hrs
0
sunhuxAuthor Commented:
With Leo's script, I got the following errors:
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "payloadlogs.LogDate" could not be bound.
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "payloadlogs.LogDate" could not be bound.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Name'.
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Severity'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Name'.

==================================================

With Vitor's script, got the following error:

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'WHERE'.

===================================================

With Scott's script, got the followg:

Msg 8120, Level 16, State 1, Line 3
Column 'dbo.payloadlogs.Severity' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 107, Level 15, State 1, Line 1
The column prefix 'ppl2' does not match with a table name or alias name used in the query.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'PayloadLogID'.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry. Should be ON and not WHERE. Corrected script:
WITH CTE_Logs (PayloadFilterID, SourceIP, DestinationIP, Action, Severity, CountPayLoad)
AS (SELECT PayloadFilterID,SourceIP, DestinationIP, Action, Severity, COUNT(1) 
	FROM dbo.payloadlogs 
	WHERE  (payloadlogs.LogDate >= '2015-02-01 00:00:00.0' AND payloadlogs.LogDate < '2015-03-01 00:00:00.0')  
	GROUP BY PayloadFilterID, SourceIP, DestinationIP, Action)

SELECT CTE_Logs.CountPayLoad, CTE_Logs.PayloadFilterID, CTE_Logs.SourceIP, CTE_Logs.DestinationIP, CTE_Logs.action, 
		CTE_Logs.Severity, payloadfilter2s.PayloadFilter2ID, payloadfilter2s.TBUID, payloadfilter2s.Name 
FROM CTE_Logs
	INNER JOIN payloadfilter2s ON CTE_Logs.PayloadFilterID = payloadfilter2s.PayloadFilter2ID 
ORDER BY CTE_Logs.CountPayLoad DESC

Open in new window

0
sunhuxAuthor Commented:
Vitor's corrected script gives the followg:

Msg 8120, Level 16, State 1, Line 2
Column 'dbo.payloadlogs.Severity' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
sunhuxAuthor Commented:
If I remove all occurrences of Severity, it works.

Small issue, I can manually look up the Severity from a table for my analysis
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
This is the problem with not testing :(
Just add 'Severity' in the GROUP BY clause:
WITH CTE_Logs (PayloadFilterID, SourceIP, DestinationIP, Action, Severity, CountPayLoad)
AS (SELECT PayloadFilterID,SourceIP, DestinationIP, Action, Severity, COUNT(1) 
	FROM dbo.payloadlogs 
	WHERE  (payloadlogs.LogDate >= '2015-02-01 00:00:00.0' AND payloadlogs.LogDate < '2015-03-01 00:00:00.0')  
	GROUP BY PayloadFilterID, SourceIP, DestinationIP, Action, Severity)

SELECT CTE_Logs.CountPayLoad, CTE_Logs.PayloadFilterID, CTE_Logs.SourceIP, CTE_Logs.DestinationIP, CTE_Logs.action, 
		CTE_Logs.Severity, payloadfilter2s.PayloadFilter2ID, payloadfilter2s.TBUID, payloadfilter2s.Name 
FROM CTE_Logs
	INNER JOIN payloadfilter2s ON CTE_Logs.PayloadFilterID = payloadfilter2s.PayloadFilter2ID 
ORDER BY CTE_Logs.CountPayLoad DESC

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.